search

OHDSI / CommonDataModel

Definition and DDLs for the OMOP Common Data Model (CDM)
https://ohdsi.github.io/CommonDataModel
897 stars 453 forks source link

unresolvable fk cycles between concept and concept_class #706

Open motey opened 3 months ago

motey commented 3 months ago

Howdy lovely CDM Community,

i am new to all this and i am in the process to create a bunch of python representations for the OMOP-CDM Model (https://github.com/DZD-eV-Diabetes-Research/dzd-omop-cdm-python-models) .

Now i ran into a problem with the CDM schema leaving me scratching my head:

In 5.4 we have the table concept which has a NOT NULL-able foreign key that references concept_class

FK Col concept_class_id in concept: https://github.com/OHDSI/CommonDataModel/blob/main/ddl/5.4/postgresql/OMOPCDM_postgresql_5.4_ddl.sql#L422

FK definition: https://github.com/OHDSI/CommonDataModel/blob/main/ddl/5.4/postgresql/OMOPCDM_postgresql_5.4_constraints.sql#L156

At the same time we have a NOT NULL-able foreign key in concept_class that references concept

FK Col concept_class_concept_id in concept_class - https://github.com/OHDSI/CommonDataModel/blob/main/ddl/5.4/postgresql/OMOPCDM_postgresql_5.4_ddl.sql#L444

FK definition: https://github.com/OHDSI/CommonDataModel/blob/main/ddl/5.4/postgresql/OMOPCDM_postgresql_5.4_constraints.sql#L159

The problem is if i want to create a new row in concept i need an existing concept_class to reference but if i want to create concept_class i need an existing concept to reference but if i want to create a new concept... you get the point :)

How am i supposed to insert concept data without having some cumbersome scripts that will disable constraints under certain circumstances (which runs against the whole idea of having a data model with constraints in the first place)

I could not find any information on how to solve this in a cleanly manner. if anyone has any hints for me, I would be very grateful. cheers.

clairblacketer commented 2 months ago

Hi @motey that is a great question. Usually when I load vocabulary, I load it first and then apply the constraints. Especially in postgres it can be very slow to load data into a table that has constraints already applied. @aostropolets , @dimshitc do you guys have any suggestions related to constraints?