CCMDB Data Structure

This article explains how our tables interlink.

Structure

actual data

• L Log table links to the following on D_ID
  • L_PHI table
  • L_CCI_Picklist table
  • L_CCI_Component table
  • L_Como table
  • L_Dxs table
  • L_Labs_Flowsheet table
  • L_Pharm_Flowsheet table
  • L_TISS Item table
  • L_TmpV2 table

support 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
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
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.xxx= s_CCI_Picklist.xxx

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_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

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:

• 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

Created/Derived Data

• Created Variables common table
• Created Variables CC table