Cycles in the ER (Entity Relationship) model

[lgautier]

Hi,

There seems to be cycles in the ER (Entity Relationship) model. For example, with VOCAB in v6.0 it understand it to look like below (a possible additional issue is that cardinality of relationships is not documented - I am assuming that the intent is to have one-to-many):

erDiagram

   CONCEPT {} 
   CONCEPT_CLASS {}
   DOMAIN {}
  VOCABULARY {}

  CONCEPT_CLASS ||--o{ CONCEPT: "is a"
  CONCEPT ||--o{ CONCEPT_CLASS: "is a"
  CONCEPT ||--o{ DOMAIN: "belongs to"
  CONCEPT ||--o{ VOCABULARY: "adapted from"
  DOMAIN ||--o{ CONCEPT: "belongs to"
  VOCABULARY ||--o{ CONCEPT: "belong to"

Loading

I believe that the intent here is to capture ontologies, or trees (disjoint or not). A table that solely capture the edges of that graph would be more standard / get closer to a Normal Form.

[cgreich]

@lgautier:

No. The ontology is captured in CONCEPT (nodes) and CONCEPT_RELATIONSHIP (edges). Concept Class, Domain and Vocabulary are purely categories and don't mean to achieve anything else. All "edges" are "belong to"/"adapted from". Sure, the relationships to these could have been done through CONCEPT_RELATIONSHIP, but that would have been an overkill, making our standard queries a lot more kludgy. They are strictly many to one - each concept has only one Concept Class, Domain or Vocabulary.

Well, the latter is not 100% true, because non-standard Concepts can have more Domains (not the other ones). But instead of allowing more than one relationship we created awkward Condition/Measurement combination Domains. But since that is not allowed for standard concepts, and they are all that matters for analyzing the data, it is pretty much as said.

Makes sense?

[lgautier]

Thanks for the answer. I seem to miss or misunderstand something.

Here is an example of what I am seeing in the CSV files:

Row 366 in OMOP_CDMv6.0_Field_Level.csv declares for the table CONCEPT a field/column called VOCABULARY_ID that is a foreign key pointing to VOCABULARY.VOCABULARY_ID (as stated by fkTableName and fkFieldName).

CommonDataModel/inst/csv/OMOP_CDMv6.0_Field_Level.csv

Line 366 in d83d48c

dose_era,person_id,Yes,bigint,NA,NA,No,Yes,PERSON,PERSON_ID,NA,NA,NA

Conversely, Row 377 in OMOP_CDMv6.0_Field_Level.csv declares for the table VOCABULARY a field/column called VOCABULARY_CONCEPT_ID that is a foreign key pointing to CONCEPT.CONCEPT_ID.

CommonDataModel/inst/csv/OMOP_CDMv6.0_Field_Level.csv

Line 377 in d83d48c

instances of Condition Occurrences.

This creates a cycle of relationships in the schema definition.

[lgautier]

I understand that modeling all this is really hard, and that special cases make it even harder to keep things simple.

I might be further wrong but it feels like the CDM tries to achieve the incredibly difficult task of being at the same time:

1. a data model
2. a semantic model
3. implement complex constraints / business logic though table definitions and their relationship

I am initially looking at it under the (narrow) lens of a data model (so point 1/). I have written code to transpile the CSV definitions into ORM definitions (Python / SQLAlchemy) as an experiment: can an ORM definition be a way to define a data model that is database agnostic and can be supplemented with constraints / business logic (so point 3/) described as code (in the same language) to allow direct use during ETL, and provide coding agents with precise technical context (code).

I am at the stage where the resulting ORM definitions I obtain seem to make schema definition checking tools nervous, or error.

[cgreich]

Hahahaha. I feel your pain.

But really, the model is not there to serve as a textbook example. It's purpose is a pretty narrow use case of calculating rates of medical entities, comparing them and drawing some statistics, based on very hampered and botched in various ways secondary use data. And for that it's the best there is.

[cgreich]

Regarding the cycle: Correct, it is a cycle. But in reality, it is only built in one way direction.

Reason we introduced the second arm (vocabulary -> concept) is to support information models. They want to be able to traverse the whole thing based on the Concept-Concept Relationship lettuce. They don't want to take the CONCEPT_CLASS, DOMAIN and VOCABULARY tables into account.

Yes, another compromise. But for the main use case it is pretty irrelevant.

[lgautier]

Hahahaha. I feel your pain.

But really, the model is not there to serve as a textbook example. It's purpose is a pretty narrow use case of calculating rates of medical entities, comparing them and drawing some statistics, based on very hampered and botched in various ways secondary use data. And for that it's the best there is.

Thanks. I am familiar with that type of data (RWD), and having also worked a few other types of data I am also painfully aware that almost nothing is quite simple with that type of data.

To clarify, my motivation here is to try leverage within reason what was designed by database engineering communities to minimize what can be ambiguous and go wrong during the port/transformation of existing dataset to the common data model, and during the subsequent consumption of data by tools & query templates. The objective is not to get to a Boyce-Codd normal form just out of theoretical considerations.

In practical terms, here is what the cycles mean is added inconvenience on the database operation side (e.g., deferring foreign key integrity checks in some way when writing data). Sometimes cycles can be hard to avoid, or more practical to have, but here it feels that they could be easily avoided.

If CONCEPT can be thought at ontology tree to which bipartite (multipartite?) graph addition (grouping with CONCEPT_CLASS, DOMAIN and VOCABULARY) this modeling a graph that typically avoids cycle. I don't think that there should be concerns about added complexity in queries. On the contrary, Recursive Common Table Expressions provide expressive ways to perform graph traversal queries/subqueries for example.

I realize that the discussion might be about more general points than the mere presence of cycles. Let me know if it should be moved elsewhere.

[lgautier]

Regarding the cycle: Correct, it is a cycle. But in reality, it is only built in one way direction.

Reason we introduced the second arm (vocabulary -> concept) is to support information models. They want to be able to traverse the whole thing based on the Concept-Concept Relationship lettuce. They don't want to take the CONCEPT_CLASS, DOMAIN and VOCABULARY tables into account.

Thanks. I would like to read queries / templates of this to try understand this better. Can you share, or point me to, examples?

[cgreich]

Yes, the dicussion should be probably had in the Forum.

Not sure what you think, @lgautier, but apart from the concept stuff the normalization ain't so bad. I think we are mostly 3N, except for a few ugly corners (drug dose, cost table, funny cycles between provider, care site, visit and patient). Boyce-Codd would be hardly achievable, because all the medical entities have tons of dependencies all over the place (one example the infamous "female prostatectomy" case used in quality checks).

I would like to read queries / templates of this to try understand this better.

Start here: https://github.com/OHDSI/QueryLibrary. It's SQL, the R is just a wrapper to make it database independent. You will see, nobody ever uses the concepts reflecting concept classes, domains or vocabularies. They barely use those reference tables, because you mostly need them for displaying. Same is true for concept relationships, where the refernce table is RELATIONSHIP: In addition to the names, it contains some hierarchy flags.

[lgautier]

Yes, the dicussion should be probably had in the Forum.

Thanks. I'll create a post with a pointer to here.

Not sure what you think, @lgautier, but apart from the concept stuff the normalization ain't so bad. I think we are mostly 3N, except for a few ugly corners (drug dose, cost table, funny cycles between provider, care site, visit and patient). Boyce-Codd would be hardly achievable, because all the medical entities have tons of dependencies all over the place (one example the infamous "female prostatectomy" case used in quality checks).

To clarify, I only mentioned normalization to indicate that my aim was not a theoretical pursuit, and gave the example of Boyce-Codd not being the objective as an illustration.

I brought up the cycles because they made an ORM mapper (SQLAlchemy) immediately trip. I understand that can't always be avoided in practice, and ORMs have manual toggles to allow them, but I was not clear about why they must be present in the CDM. The reason for this is that I was looking at mitigating the unpredictability coming from code generation by AI with increased or more explicit checks and constrains.

My current understanding is that CONCEPT, VOCABULARY capture data typically stored as a graph. If thinking about it explicitly as a graph:

• de-normalization, "ugly corners", or other practical optimizations could be smoothed out, limiting coding agents' ability to "do the wrong thing"
• recent SQL additions such as recursive expressions could provide expressive ways to perform graph operations such as traversal.

I would like to read queries / templates of this to try understand this better.

Start here: https://github.com/OHDSI/QueryLibrary. It's SQL, the R is just a wrapper to make it database independent. You will see, nobody ever uses the concepts reflecting concept classes, domains or vocabularies. They barely use those reference tables, because you mostly need them for displaying. Same is true for concept relationships, where the refernce table is RELATIONSHIP: In addition to the names, it contains some hierarchy flags.

Thanks. I'll study that code.