CCMDB Data Structure

From CCMDB Wiki
Jump to navigation Jump to search

This article explains how our tables interlink. Click on any of the table names to find out more about what data is contained in them and how it is encoded. For information on data fields, see Auto Data Dictionary. For information on data encoded in the Entity–attribute–value model of the L_Tmp_V2 table, see Projects.

Actual Data

All of these are 1-many, except for the link between L_Log table and L_PHI table, which is 1-to-1. The only reason for the split in table is for de-identification in the Centralized data.mdb/PHI.mdb, this data is all stored in L_Log in CCMDB.mdb. Also see Entity Relationship Diagram for the Critical Care and Medicine Database for a visual representation.

Support/Dropdown Tables

L_Log table

Some fields in L Log table draw from support tables as follows:

field field name position s_table
Province field R_Province 16 S Provinces table
Pre-admit Inpatient Institution field Pre_admit_Inpatient_Institution 36 S pre acute living situation table
Previous Location field Previous_Location 37 S dispo table
Previous Service field Previous_Service 38 S_previous_service table
Service/Location field Service_Location 40 S dispo table
Dispo field dispo 41 S dispo table
RecordStatus field RecordStatus 80 s_RecordStatus table
ADL Bathing ADL_Bathing 49 S ADLOptions table
ADL Dressing ADL_Dressing 50 S ADLOptions table
ADL Toiletting ADL_Toiletting 51 S ADLOptions table
ADL Transfering ADL_Transfering 52 S ADLOptions table
ADL Continence ADL_Continence 53 S ADLOptions table
ADL Feeding ADL_Feeding 54 S ADLOptions table
Admit Type for APACHE II Ap_AdmitType 55 S_AP_AdmitType table
Chronic Health APACHE Ap_Chronic 56 S AP Chronic table
FiO2 Ap_FIO2 67 S FIO2 table
AP Eye field AP_Eye 63 s_GCS table
AP Motor field AP_Motor 63 s_GCS table
AP Verbal field AP_Verbal 63 s_GCS table

L_CCI_Picklist table

L_CCI_Picklist table links to s_CCI_Picklist table as

  • L_CCI_Picklist.CCI_Picklist_code= s_CCI_Picklist.CCI_Picklist_code

L_CCI_Component table

L_CCI_Component table links to s_CCI_1 table as

  • L_CCI_Component.CCI_1_Code= s_CCI_1.CCI_1_Code

L_CCI_Component table links to s_CCI_2 table as

  • L_CCI_Component.CCI_2_Code= s_CCI_2.CCI_2_Code

L_ICD10 table

L_ICD10 table links to s_ICD10 table as

  • L_ICC10.ICD10_code = S_ICD10.ICD10_code

L_TmpV2 table

The L_TmpV2 table links on both of the following two fields together with the s_tmp table table:

  • L_TmpV2.project = s_Tmp.Study
  • L_TmpV2.Item = S_tmp.Value

Legacy Data

L_Como table

L_Como table links to s_AllDiagnoses table as

  • L_Como.ComorbidCode = S AllDiagnoses.DiagnosisCode

L_Dxs table

L_Como table links to s_AllDiagnoses table as

  • L_Dxs table.DiagnosisCode = S_AllDiagnoses.DiagnosisCode

Created/Derived Data

Related articles

Related articles: