Why are there no primary keys for certain tables?

[ghost]

Hi everyone,

I'm looking into MIMIC-IV 2.0 and I see that many tables do not have a primary key yet alone a dedicated ID field. And some others use composite keys. What was your train of thought when designing the database like this?

Thank you for your reply :-)

[tompollard]

Thanks @fantasticle, it's a work in progress and not perfect by any means! Please add specific suggestions for improvement and we will try to incorporate them into the next version.

[tompollard]

Thanks @fantasticle, this seems like something we should think about a little more. Ensuring that we're compatible with ORMs is definitely worthwhile.

In the past we included a row_number column in all tables which sounds like it would be roughly equivalent of your suggestion (i.e. a unique ID for each row in the table) but eventually decided that it wasn't especially helpful.

Before we make any changes to the data itself, it would be good to find some specific examples of how the current structure is problematic.

Would you be able to provide a sample chunk of code that demonstrates an ORM (e.g. SQLAlchemy, Python) failing on the MIMIC-IV demo (https://www.physionet.org/content/mimic-iv-demo/)?

Side note, but perhaps we could provide an ORM data structure as part of the build scripts at: https://github.com/MIT-LCP/mimic-code/tree/main/mimic-iv/buildmimic

@alistairewj may have more to say about this!

[alistairewj]

There are primary keys in every table, they are just composite keys comprising multiple columns. For example the primary key for chartevents is stay_id, charttime, itemid,... and maybe value, I can't remember off the top of my head.

The TODO here I would say is to recreate the constraint.sql file we used to have which made these keys explicit.

[tompollard]

@alistairewj There is a constraints file at https://github.com/MIT-LCP/mimic-code/blob/main/mimic-iv/buildmimic/postgres/constraint.sql but I don't see composite primary keys e.g. for chartevents.

@fantasticle is the ORM that you use able to handle composite primary keys? If we provided these constraints, would it solve the problem?

[ghost]

There are primary keys in every table, they are just composite keys comprising multiple columns. For example the primary key for chartevents is stay_id, charttime, itemid,... and maybe value, I can't remember off the top of my head.

The TODO here I would say is to recreate the constraint.sql file we used to have which made these keys explicit.

@alistairewj This would certainly help for any DB visualization tool (e.g. DBeaver). Unfortunately tools like Django cannot work with composite primary keys as stated in their official documentation:

Each model requires exactly one field to have primary_key=True (either explicitly declared or automatically added).
https://docs.djangoproject.com/en/4.0/topics/db/models/#automatic-primary-key-fields

@tompollard I will give my best to add an example the following days.

[ghost]

@alistairewj There is a constraints file at https://github.com/MIT-LCP/mimic-code/blob/main/mimic-iv/buildmimic/postgres/constraint.sql but I don't see composite primary keys e.g. for chartevents.

@fantasticle is the ORM that you use able to handle composite primary keys? If we provided these constraints, would it solve the problem?

I'm using Django - therefore it is unfortunately not ☹️. But I'd say independent of my limitation using an ORM tool defining primary keys (composite or not) in the .sql-files would be a good thing 👍

[ghost]

And while we are at it - the LABEVENTS table does not have any direct links to ICUSTAYS. This would be super helpful in the future!