OHDSI Home | Forums | Wiki | Github

FACT_RELATIONSHIP Table - Domain Mappings


We are interested in using the FACT_RELATIONSHIP table to represent clinical reasoning. As part of the process we would like to aggregate similar concepts appearing in the medical record. For example, if a patient has numerous diagnosis of diabetes type 2, NLP mentions of type 2 diabetes we would like to create a “rolled up” concept using fact relationship table. This could then be used in a treatment relationship with a rolled up version of metformin for example. Is this a reasonable use case? I would love to hear if anyone is using this table in the real world.

Implementing this also appears tricky as FACT_RELATIONSHIP is supposed to provide mappings to specific tables by using the DOMAIN table. This suggests that the domain_name in the DOMAIN table (for example ‘Person’) should map to the PERSON table. However, the pre-existing data for DOMAIN does NOT include all tables (at least in our OMOP load) and it may be out of sync as per:

Would it be reasonable to add a DOMAIN for each table currently in the schema? Or is this something that each site should do on a as needed basis?

Thanks for any thoughts,


Thought 1) Use the domain concepts, that is what they are there for. Don’t worry if non-standard, although maybe the vocabulary group should make them standard.

Thought 2) Look into using the episode tables, they seem more suited to your use case than the Fact Relationship table.

Thanks for your quick response Don!

We will add in those domain concepts. Are there rules like use ID#s > X for adding our site DOMAIN tables? I think for concepts I saw use ID#s greater than 2 billion…

Yes, we were considering using Episodes - they do look like a better fit, I just need to work out the details.

@JohnOsborne Not sure we are on the same page. I was suggesting using the existing concepts for domains and you are suggesting adding new “domain” concepts. What is missing from the existing domain concepts that you need to add new concepts?

I don’t see VISIT_OCCURENCE or VISIT_DETAILS represented as domain concepts, I’m still working it out, but I believe I would need to add those if I want to make a reference to those tables in FACT_RELATIONSHIP ?

However, I do see PERSON as a domain concept (id=56). Do I need to add a row in the DOMAIN table for this since there is none by default? I don’t understand how the relationship between DOMAIN.domain_name and the table name in OMOP works. Is it suppose to contain the table name? Is it implicit?

Yes, the relationship between Domain and Table Name is implied. There is an effort underway better define the table/column when one table refers to a record in another table. There are now concepts that represent the CDM tables and table columns. So rather then the implied relationship that domain_concept_id_1 = ‘Procedure’ references the Procedure Occurrence table the value in Fact Relationship will be the concept 114730 that explicitly references the Procedure Occurrence table.

Rather than creating your own concept for Person and the other tables, I suggest you use this set of concepts

select *
from concept
where vocabulary_id = ‘CDM’ and concept_class_id = ‘Table’;

Yes, I see now, thank you!

Welcome to OHDSI, @JohnOsborne !

This would be an “off label” use of the Fact Relationship table. And not a very good off label use. The Fact Relationship table is really good to connect mom-baby relationships, hierarchical care site relationships, systolic & diastolic blood pressure measurements, or other clinical or health system records in the CDM. It shouldn’t be used to connect concept_ids. We have the Vocabulary tables, specifically Concept Ancestor and Concept Relationship to represent relationships between concepts. Concept Ancestor “rolls up” concepts.

Please explain your use case a little more and we (the community) will help you find a better way to represent or query your data. Just off the top of my head, creating concept sets and then phenotypes for Type 2 Diabetes might serve your use case. And for drugs, I always advocate the use of the Concept Ancestor table when looking for specific ingredients. Then narrow down the results based on the drug’s attributes (dose, form, etc.). Do you have Atlas installed?

Just saw this post now - thanks for your feedback Melanie.

We definitely have an off-label use of OMOP - we are creating a patient specific database as part of an NIH R21 grant that will contain elements of clinical reasoning extracted from notes. For example, why a particular medication was given to a patient, what condition a physician thinks is causing a particular symptom, etc…

My background is NLP and part of the issue is that the NOTE_NLP table does not capture relationships between concepts. Only modifiers (temporal or otherwise) to that concept. So if someone runs relation extraction software on clinical text, there really isn’t a good place to put those results. That’s why we looked at modifying the FACT_RELATIONSHIP table to allow relationships not between concepts, but between instances.

Based on what you are saying though, it sounds like we show consider adding an entirely new table (NLP_RELATIONSHIP) to capture relationships in text, or between a comment in text and a lab value? For example a physician believing a particular lab result rules out a condition.

We don’t have a local install of ATLAS I am aware of.

@JohnOsborne have you connected with the NLP Working Group? (wiki page) I haven’t dropped in there since the start of the year but I believe they have related goals.

No, I haven’t connected although I know some of the folks - I think I may have attended a meeting a long time back. I assume these are the folks I should bring up lack of relation extraction support in the current version of OMOP?