CCMDB Data Structure: Difference between revisions

From CCMDB Wiki
Jump to navigation Jump to search
m (→‎Structure: added legacy links)
m (Text replacement - "S Tmp" to "s_tmp table")
 
(31 intermediate revisions by 2 users not shown)
Line 1: Line 1:
This article explains how our tables interlink.  
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]].


== Structure ==
== Actual Data ==
=== 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 Log table]] links to the following on [[D_ID]]
** [[L PHI table]]
** [[L_PHI table]]
** [[L Como table]]
** [[L_CCI_Picklist table]]
** [[L Dxs table]]
** [[L_CCI_Component table]]
** [[L Labs Flowsheet table]]
** [[L_Labs_Flowsheet table]]
** [[L Pharm Flowsheet table]]
** [[L_Pharm_Flowsheet table]]
** [[L TISS Item table]]
** [[L_TISS Item table]] (in different file)
** [[L TmpV2 table]]
** [[L_TmpV2 table]]
** [[L_Labs_DSM table]] (in different file)


=== support tables ===
== Support/Dropdown Tables ==
==== [[L_Log table]] ====
=== [[L_Log table]] ===
Some fields in [[L Log table]] draw from support tables as follows:  
Some fields in [[L Log table]] draw from support tables as follows:  
{| class="wikitable sortable"
{| class="wikitable sortable"
Line 19: Line 21:
! field !! field name !! position !! s_table
! field !! field name !! position !! s_table
|-
|-
| [[Province Field]] || R_Province ||{{#show: Province Field | ?DataElementSortIndex}}|| [[S Provinces table]]
| [[Province field]] || R_Province ||{{#show: Province field | ?DataElementSortIndex}}|| [[S Provinces table]]
|-
|-
| [[Pre-admit Inpatient Institution field]] || Pre_admit_Inpatient_Institution|| {{#show: Pre-admit Inpatient Institution field| ?DataElementSortIndex}} || [[S pre acute living situation table]]
| [[Pre-admit Inpatient Institution field]] || Pre_admit_Inpatient_Institution|| {{#show: Pre-admit Inpatient Institution field| ?DataElementSortIndex}} || [[S pre acute living situation table]]
Line 31: Line 33:
| [[Dispo field]] || dispo|| {{#show: Dispo field| ?DataElementSortIndex}} || [[S dispo table]]
| [[Dispo field]] || dispo|| {{#show: Dispo field| ?DataElementSortIndex}} || [[S dispo table]]
|-
|-
| [[RecordStatus field]] || RecordStatus || {{#show: RecordStatus field| ?DataElementSortIndex}} || [[S RecordStatus]]
| [[RecordStatus field]] || RecordStatus || {{#show: RecordStatus field| ?DataElementSortIndex}} || [[s_RecordStatus table]]
|-
|-
| [[ADL Bathing]] || ADL_Bathing || {{#show: ADL Bathing| ?DataElementSortIndex}} || [[S ADLOptions table]]
| [[ADL Bathing]] || ADL_Bathing || {{#show: ADL Bathing| ?DataElementSortIndex}} || [[S ADLOptions table]]
Line 45: Line 47:
| [[ADL Feeding]] || ADL_Feeding|| {{#show: ADL Feeding| ?DataElementSortIndex}} || [[S ADLOptions table]]
| [[ADL Feeding]] || ADL_Feeding|| {{#show: ADL Feeding| ?DataElementSortIndex}} || [[S ADLOptions table]]
|-
|-
| [[Admit Type for APACHE II]] || Ap_AdmitType|| {{#show: Admit Type for APACHE II| ?DataElementSortIndex}} || [[S AP AdmitType]]
| [[Admit Type for APACHE II]] || Ap_AdmitType|| {{#show: Admit Type for APACHE II| ?DataElementSortIndex}} || [[S_AP_AdmitType table]]
|-
|-
| [[Chronic Health APACHE]] || Ap_Chronic|| {{#show: Chronic Health APACHE| ?DataElementSortIndex}} || [[S AP Chronic]]
| [[Chronic Health APACHE]] || Ap_Chronic|| {{#show: Chronic Health APACHE| ?DataElementSortIndex}} || [[S AP Chronic table]]
|-
|-
| [[FiO2]] || Ap_FIO2|| {{#show: FiO2| ?DataElementSortIndex}} || [[S FIO2]]
| [[FiO2]] || Ap_FIO2|| {{#show: FiO2| ?DataElementSortIndex}} || [[S FIO2 table]]
|-
|-
| [[AP Eye field]] || AP_Eye|| {{#show: AP Eye field| ?DataElementSortIndex}} || [[S GCS]]
| [[AP Eye field]] || AP_Eye|| {{#show: AP Eye field| ?DataElementSortIndex}} || [[s_GCS table]]
|-
|-
| [[AP Motor field]] || AP_Motor|| {{#show: AP Motor field| ?DataElementSortIndex}} || [[S GCS]]
| [[AP Motor field]] || AP_Motor|| {{#show: AP Motor field| ?DataElementSortIndex}} || [[s_GCS table]]
|-
|-
| [[AP Verbal field]] || AP_Verbal|| {{#show: AP Verbal field| ?DataElementSortIndex}} || [[S GCS]]
| [[AP Verbal field]] || AP_Verbal|| {{#show: AP Verbal field| ?DataElementSortIndex}} || [[s_GCS table]]
|}
|}


==== [[L_TmpV2 table]] ====
=== [[L_CCI_Picklist table]] ===
The [[L_TmpV2 table]] links on both of the following two fields together with the [[S Tmp]] 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.project = s_Tmp.Study
* L_TmpV2.Item = S_tmp.Value
* L_TmpV2.Item = S_tmp.Value


==== [[L_Como table]] ====
== Legacy Data ==
[[L_Como table]] links to [[S AllDiagnoses]] table as  
* [[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_Como.ComorbidCode = S AllDiagnoses.DiagnosisCode


==== [[L_Dxs table]] ====
=== [[L_Dxs table]] ===
[[L_Como table]] links to [[S AllDiagnoses]] table as  
[[L_Como table]] links to [[s_AllDiagnoses table]] as  
* L_Dxs.DiagnosisCode = S AllDiagnoses.DiagnosisCode
* L_Dxs table.DiagnosisCode = S_AllDiagnoses.DiagnosisCode


=== Legacy Data ===
== Created/Derived Data ==
* [[L Pharmacy pre 2012 table]]
* [[Created_Variables_Common_2021 table]]
* [[L TISS76 table]]
* [[Created_Variables_CC_2021 table]]
* [[L Labs pre 2014 Aug table]]


== Related articles ==
{{Related Articles}}


[[Category:Data structure | *]]
[[Category:Data structure | *]]

Latest revision as of 09:41, 2023 May 18

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:
Retrieved from "https://ccmdb.kuality.ca/index.php?title=CCMDB_Data_Structure&oldid=159452"