search

clif-consortium / CLIF

Apache License 2.0
4 stars 4 forks source link

CLIF as a relational SQL-like database #28

Open 08wparker opened 1 month ago

08wparker commented 1 month ago

we should make CLIF "SQL ready" because SQL databases have an explicit relational database structure that will help us achieve the goal of having each hospitalization represented coherently in CLIF.

patient: patient_id is the primary key hospitalization: hospitalization_id is the primary key. patient_id is a foreign key to link hospitalizations to patients vitals: hospitalization_id is a foreign key to link each set of vitals to a specific hospitalization. labs: hospitalization_id also serves as a foreign key to associate lab results with the corresponding hospital stay.

I also like this:

Foreign Key Constraints: By using SQL, you can enforce foreign key constraints to maintain referential integrity. This ensures that every hospitalization_id in the vitals, labs, or medications tables must exist in the hospitalization table. It helps prevent orphaned records (e.g., vitals records that reference a non-existent hospitalization).

SQL queries will be a very efficient way to do things like identify sepsis, etc.

Let's add a running to do list to this issue. I think we are pretty close!

08wparker commented 1 month ago
  1. Ensure each table would have a valid primary key (can be combination of multiple fields)
  2. use SQL datatypes