CCMDB Data Structure

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.

• L Log table links to the following on D_ID
  • L_PHI table
  • L_CCI_Picklist table
  • L_CCI_Component table
  • L_Labs_Flowsheet table
  • L_Pharm_Flowsheet table
  • L_TISS Item table (in different file)
  • L_TmpV2 table
  • L_Labs_DSM table (in different file)

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 Pharmacy pre 2012 table
• L TISS76 table
• L Labs pre 2014 Aug table
• L_TISS_Item table (in different file)

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

• Created_Variables_Common_2021 table
• Created_Variables_CC_2021 table