Our team plans to adopt OMOP for our data storage, but we want a few changes to the 5.4 DDL to fit our needs. We use PostgreSQL, and the key adjustments we’re looking at are:
Use IDENTITY in place of integer primary keys to so the database handles ID creation instead of inserting values manually.
Use ON DELETE CASCADE to foreign keys. When a person is deleted, we want all related records linked by that person to be removed as well.
We want to keep compatibility with existing OHDSI tools like HADES. From what I think, these changes should not affect it since it doesn’t modify the CDM, but I would like to hear from developers who have experiences in those tools
If I understand correctly, you are talking about decorators that would not change the actual type of the fields. So instead of
CREATE TABLE person_id (
person_id INTEGER NOT NULL,
...
You would get
CREATE TABLE person_id (
person_id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
...
So the underling type would remain an integer.
Similarly, the ON DELETE CASCADE seems like just a way to help enforce the required constraints.
I suspect both are fine. Typically, people don’t edit data in the CDM, but rather have a full or incremental ETL process from some external source into the CDM that is executed when needed. Data edits such as adding or deleting persons and clinical events takes place in the source data. The ETL process usually generates IDs needed in the CDM.
Yes, that’s basically what we’re trying to do. I suppose using the DB in the traditional manner fits our use case better, but we’ll see if we run into any problems. Thank you!