Question about Fact Relationship

2

Hello @Vines,

First, a brief description of fields:

  1. domain_concept_id_1 — this field indicates which CDM domain the 1st record (fact) belongs to. As this is a concept_id, you’ll have to look it up in Concept table. These concepts have domain_id=“Metadata” (because they basically refer to CDM’s own structure) and vocabulary_id=“Domain”. Here’s an example.
  2. fact_id_1 — this is not a concept, but a unique record ID in another CDM table. For example, if the 1st fact is from Measurement table, its measurement_id value will go here.
  3. domain_concept_id_2 — similar to domain_concept_id_1, this is a concept which indicates the CDM domain of the 2nd fact.
  4. fact_id_2 — again, a unique ID of 2nd fact in another table. This another table may be the same as for the 1st fact, or a different one.
  5. relationship_concept_id — a concept which provides more context on how/why these two facts are connected/relate to each other. For example, if the 1st fact is myeloma (Condition) and the 2nd is lab test used to define myeloma progression (Measurement), the concept “Condition relevant to” gives a description of the relationship.

Usage example: let’s say we have 1st fact of “Bacteria identified in Urine by Culture” (Measurement) and 2nd fact of “Nitrofurantoin [Susceptibility]” (also Measurement). For simplicity, let’s say their measurement_id values are 1 and 2, respectively.

domain_concept_id_1 fact_id_1 domain_concept_id_2 fact_id_2 relationship_concept_id
21 1 21 2 581437

Here, both domain_id are 21 “Measurement”, and relationship_concept_id=581437 stands for “Child to Parent Measurement”.
Bear in mind that relationships are directional (see convention #1 here), so a “reverse” record is also needed:

domain_concept_id_1 fact_id_1 domain_concept_id_2 fact_id_2 relationship_concept_id
21 2 21 1 581436

relationship_concept_id=581436 is “Parent to Child Measurement”.

How to find relatinoship_concept_id: domain=“Metadata”, concept_class=“Relatinship”.

1 Like