Relationship.relationship_name != concept.concept_name

[Sigfried_Gold]

I’m working on some vocabulary browsing tools and poking around the vocabulary tables. I’m wondering if anyone can explain this to me.

The relationship table has two mostly redundant columns:

• relationship_name
• relationship_concept_id

For 381 of the 388 rows in relationship, the concept_name related to the relationship_concept_id is the same as the relationship_name, but 7 rows have different values:

select row_number() over (order by 1,2) as rn,
       r.relationship_name, 
       c.concept_name 
from :cdm.relationship r 
join :cdm.concept c on r.relationship_concept_id = c.concept_id 
where r.relationship_name != c.concept_name;

 rn |              relationship_name              |                   concept_name
----+---------------------------------------------+---------------------------------------------------
  1 | ATC to RxNorm (RxNorm)                      | ATC to RxNorm (FDB)
  2 | ICD-9-Procedure to SNOMED equivalent (OMOP) | ICD9 procedure to SNOMED equivalent (OMOP)
  3 | Standard to Non-standard map (OMOP)         | Mapping relationship from Standard Concept (OMOP)
  4 | Non-standard to Standard map (OMOP)         | Mapping relationship to Standard Concept (OMOP)
  5 | RxNorm to ATC (RxNorm)                      | RxNorm to ATC (FDB)
  6 |  RxNorm to SNOMED equivalent (RxNorm)       | RxNorm to SNOMED equivalent (RxNorm)
  7 | SNOMED to ICD-9-Procedure equivalent (OMOP) | SNOMED to ICD9 procedure equivalent (OMOP)

For rows 2, 6 and 7 the differences are purely typographical inconsistencies and should be fixed. Rows 3 and 4 seem to be insubstantial differences in phrasing and should also probably be reconciled. Rows 1 and 5 seem to be disagreements about the source of the relationship, and maybe one of these is right and the other is wrong.

So, when I started composing this email I was going to ask whether there was a reason for these redundant fields and if the small number of inconsistencies were meaningful. But looking more closely it appears the inconsistencies are not meaningful and could be fixed. And, if they are fixed, is there any reason to retain the redundant fields? Presumably the one we should get rid of is relationship_concept_id.

(I was going to post this to Vocab Users, but I guess it belongs in CDM Builders?)

[Christian_Reich]

For rows 2, 6 and 7 the differences are purely typographical inconsistencies and should be fixed

Will do.

Rows 3 and 4 seem to be insubstantial differences in phrasing and should also probably be reconciled

What’s wrong?

Rows 1 and 5 seem to be disagreements about the source of the relationship, and maybe one of these is right and the other is wrong.

Will fix.

Presumably the one we should get rid of is relationship_concept_id.

Several points:

1. Relationship_concept_id is a nod towards a closed information system, where all the semantic meanings are in the concept table. There was a discussion about 2 years ago to that end. However, that would have created performance issues, and the CONCEPT table doesn’t contain all the fields the RELATIONSHIP table has. This was the compromise (both a special reference table and a record in the general CONCEPT table).
2. The redundancies between relationship_id and relationship_name: You are right, they don’t make too much sense. However, there is an unfinished proposal on the table to simplify all the relationships to just 12, which has stopped all improvements on the current ones:

• Hierarchical up
• Hierarchical down
• Hierarchical lateral from lower to higher part of the hierarchy
• Hierarchical lateral from higher to lower part of the hierarchy
• Hierarchical up and lateral (uphill)
• Hierarchical down and lateral (downhill)
• and all of them again but not hierarchical

We can easily fix relationship_name (and the corresponding concept_name), if that helps your UI. We cannot touch the relationship_id without causing a huges disruption.

[Sigfried_Gold]

Another confusion or maybe mistake:

select * from :cdm.relationship 
where is_hierarchical = '0' and defines_ancestry = '1';

 defines_ancestry | is_hierarchical | relationship_concept_id |   relationship_id    |                relationship_name                | reverse_relationship_id
------------------+-----------------+-------------------------+----------------------+-------------------------------------------------+-------------------------
 1                | 0               |                44818716 | Has tradename        | Has tradename (RxNorm)                          | Tradename of
 1                | 0               |                44818805 | MedDRA - SNOMED eq   | MedDRA to SNOMED equivalent (OMOP)              | SNOMED - MedDRA eq
 1                | 0               |                44818810 | ATC - RxNorm         | ATC to RxNorm (RxNorm)                          | RxNorm - ATC
 1                | 0               |                44818820 | Reformulated in      | Reformulated in (RxNorm)                        | Reformulation of
 1                | 0               |                44818900 | SNOMED - ICD9P eq    | SNOMED to ICD-9-Procedure equivalent (OMOP)     | ICD9P - SNOMED eq
 1                | 0               |                44818902 | SNOMED - CPT4 eq     | SNOMED to CPT-4 equivalent (OMOP)               | CPT4 - SNOMED eq
 1                | 0               |                44818926 | ATC - RxNorm name    | ATC to RxNorm equivalent by concept_name (OMOP) | RxNorm - ATC name
 1                | 0               |                44818944 | VAProd - RxNorm eq   | VA Product to RxNorm equivalent (NDF-RT)        | RxNorm - VAProd eq
 1                | 0               |                44818951 | Multilex ing of      | Ingredient of (Multilex)                        | Multilex has ing
 1                | 0               |                44818953 | Multilex - RxNorm eq | Multilex to RxNorm equivalent (OMOP)            | RxNorm - Multilex eq
 1                | 0               |                45754827 | Has quantified form  | Has quantified form (RxNorm)                    | Quantified form of

The relationship table doc says:

Defines whether a hierarchical relationship contributes to the concept_ancestor table. These are subsets of the hierarchical relationships. Valid values are 1 or 0.

So there shouldn’t be records like this, right?

Thanks!

[Christian_Reich]

@Sigfried_Gold:

Well, is_hierarchical really means is a relationships that is meaningful in a hierarchical structure. But if you go from one vocabulary to another using a lateral step in order to continue the hierarchy there, it makes a lot of sense. Those 1s and 0s are counted in order to get the right min_levels_of_separation. So, makes a lot of sense.

But we should probably improve the documentation.

[Sigfried_Gold]

Thanks,@Christian_Reich! Actually, I don’t think you need to fix any of this for my sake. I wanted to report it in case that might be helpful, but I think I can just ignore the relationship_concept_id column since it doesn’t contain additional information.

I appreciate the explanation.

[Sigfried_Gold]

Got it, @Christian_Reich. Thanks!

[Vojtech_Huser (Vojtech (Vojta) Huser)]

+1

But we should probably improve the documentation.

[Christian_Reich]

No, @Vojtech_Huser. We will fix it.

[Christian_Reich]

Fixed.

[Sigfried_Gold]

Hi @Christian_Reich,

So, I think there’s another issue as well. In the query results below (I can send the queries if desired), the first set of records, where same_vocab is false, I believe, we see what you described: a lateral step from one vocab to another that is needed for a hierachical relationship but doesn’t constitute an actual hierarchical level.

But then we also have situations, the bottom 15 records, where there seems to be a lateral step from DPD to DPD or RxNorm to RxNorm. All of these have relationship types of ‘Has tradename’, ‘Has quantified form’, or ‘Reformulated in’. Are these appropriate? Is the situation basically the same as the others, where these relationships are part of a hierarchy but don’t constitute a hierarchical step?

is_hierarchical | defines_ancestry | same_vocab | sc_1 | sc_2 |  vocab_1   |  vocab_2   |       class_1        |       class_2        |   relationship_id    | c1_ids | c2_ids |   c
-----------------+------------------+------------+------+------+------------+------------+----------------------+----------------------+----------------------+--------+--------+--------
0               | 1                | f          | S    | S    | RxNorm     | DPD        | Branded Drug         | Quant Branded Drug   | Has quantified form  |     98 |    116 |    116
0               | 1                | f          | S    | S    | RxNorm     | DPD        | Clinical Drug        | Branded Drug         | Has tradename        |   3517 |  13639 |  13639
0               | 1                | f          | S    | S    | RxNorm     | DPD        | Clinical Drug        | Quant Branded Drug   | Has tradename        |    306 |    590 |    590
0               | 1                | f          | S    | S    | RxNorm     | DPD        | Clinical Drug        | Quant Clinical Drug  | Has quantified form  |    338 |    446 |    446
0               | 1                | f          | S    | S    | RxNorm     | DPD        | Clinical Drug Comp   | Branded Drug Comp    | Has tradename        |   2891 |  16501 |  20003
0               | 1                | f          | S    | S    | RxNorm     | DPD        | Clinical Drug Form   | Branded Drug Form    | Has tradename        |   2275 |  10132 |  10132
0               | 1                | f          | S    | S    | RxNorm     | DPD        | Quant Branded Drug   | Quant Branded Drug   | Has quantified form  |      2 |      2 |      2
0               | 1                | f          | S    | S    | RxNorm     | DPD        | Quant Clinical Drug  | Branded Drug         | Has tradename        |      8 |     10 |     10
0               | 1                | f          | S    | S    | RxNorm     | DPD        | Quant Clinical Drug  | Quant Branded Drug   | Has tradename        |      9 |     12 |     12
0               | 1                | f          | S    | S    | RxNorm     | DPD        | Quant Clinical Drug  | Quant Clinical Drug  | Has quantified form  |      5 |      6 |      6
0               | 1                | f          |      | S    | ATC        | RxNorm     | ATC 5th              | Ingredient           | ATC - RxNorm         |   2875 |   2312 |   2875
0               | 1                | f          |      | S    | ATC        | RxNorm     | ATC 5th              | Ingredient           | ATC - RxNorm name    |      5 |      1 |      5
0               | 1                | f          |      | S    | VA Product | RxNorm     | VA Product           | Branded Drug         | VAProd - RxNorm eq   |    444 |    370 |    444
0               | 1                | f          |      | S    | VA Product | RxNorm     | VA Product           | Branded Drug Form    | VAProd - RxNorm eq   |      3 |      3 |      3
0               | 1                | f          |      | S    | VA Product | RxNorm     | VA Product           | Branded Pack         | VAProd - RxNorm eq   |    244 |    233 |    244
0               | 1                | f          |      | S    | VA Product | RxNorm     | VA Product           | Clinical Drug        | VAProd - RxNorm eq   |  11050 |   8948 |  11050
0               | 1                | f          |      | S    | VA Product | RxNorm     | VA Product           | Clinical Drug Form   | VAProd - RxNorm eq   |     33 |     29 |     33
0               | 1                | f          |      | S    | VA Product | RxNorm     | VA Product           | Clinical Pack        | VAProd - RxNorm eq   |    127 |    104 |    127
0               | 1                | f          |      | S    | VA Product | RxNorm     | VA Product           | Ingredient           | VAProd - RxNorm eq   |     45 |     44 |     45
0               | 1                | f          |      | S    | VA Product | RxNorm     | VA Product           | Quant Branded Drug   | VAProd - RxNorm eq   |    252 |    213 |    252
0               | 1                | f          |      | S    | VA Product | RxNorm     | VA Product           | Quant Clinical Drug  | VAProd - RxNorm eq   |   1559 |   1186 |   1559
0               | 1                | f          |      |      | ATC        | RxNorm     | ATC 5th              | Dose Form            | ATC - RxNorm name    |      1 |      1 |      1
0               | 1                | f          |      |      | ATC        | RxNorm     | ATC 5th              | Ingredient           | ATC - RxNorm         |    129 |    113 |    129
0               | 1                | f          |      |      | VA Product | RxNorm     | VA Product           | Brand Name           | VAProd - RxNorm eq   |      6 |      6 |      6
0               | 1                | f          |      |      | VA Product | RxNorm     | VA Product           | Ingredient           | VAProd - RxNorm eq   |      5 |      5 |      5
0               | 1                | t          | S    | S    | DPD        | DPD        | Branded Drug         | Quant Branded Drug   | Has quantified form  |   2013 |   2049 |   2049
0               | 1                | t          | S    | S    | DPD        | DPD        | Clinical Drug        | Branded Drug         | Has tradename        |  11686 |  15361 |  15439
0               | 1                | t          | S    | S    | DPD        | DPD        | Clinical Drug        | Quant Branded Drug   | Has tradename        |   1235 |   1571 |   1571
0               | 1                | t          | S    | S    | DPD        | DPD        | Clinical Drug        | Quant Clinical Drug  | Has quantified form  |   1989 |   2031 |   2031
0               | 1                | t          | S    | S    | DPD        | DPD        | Clinical Drug Comp   | Branded Drug Comp    | Has tradename        |  17228 |  14593 |  50762
0               | 1                | t          | S    | S    | DPD        | DPD        | Clinical Drug Form   | Branded Drug Form    | Has tradename        |   6432 |  12525 |  12525
0               | 1                | t          | S    | S    | DPD        | DPD        | Quant Clinical Drug  | Quant Branded Drug   | Has tradename        |   1681 |   2165 |   2165
0               | 1                | t          | S    | S    | RxNorm     | RxNorm     | Branded Drug         | Quant Branded Drug   | Has quantified form  |   1707 |   1986 |   1986
0               | 1                | t          | S    | S    | RxNorm     | RxNorm     | Clinical Drug        | Branded Drug         | Has tradename        |  11624 |  20021 |  20021
0               | 1                | t          | S    | S    | RxNorm     | RxNorm     | Clinical Drug        | Quant Clinical Drug  | Has quantified form  |   1823 |   2285 |   2285
0               | 1                | t          | S    | S    | RxNorm     | RxNorm     | Clinical Drug Comp   | Branded Drug Comp    | Has tradename        |  10606 |  18893 |  27879
0               | 1                | t          | S    | S    | RxNorm     | RxNorm     | Clinical Drug Form   | Branded Drug Form    | Has tradename        |   5865 |  14680 |  14680
0               | 1                | t          | S    | S    | RxNorm     | RxNorm     | Clinical Pack        | Branded Pack         | Has tradename        |    349 |    541 |    541
0               | 1                | t          | S    | S    | RxNorm     | RxNorm     | Quant Clinical Drug  | Quant Branded Drug   | Has tradename        |   1460 |   1985 |   1985
0               | 1                | t          |      |      | RxNorm     | RxNorm     | Brand Name           | Brand Name           | Reformulated in      |     99 |     99 |     99