CCMDB Data Structure: Difference between revisions

(27 intermediate revisions by 2 users not shown)

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.accdb]]/[[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_ICD10 table]]

** [[~~L Dxs~~ table]]

** [[L_CCI_Picklist table]]

** [[L_Labs_Flowsheet ~~table table~~ table]]

** [[L_CCI_Component table]]

** [[L_Pharm_Flowsheet table table]]

** [[L_Labs_Flowsheet table]]

** [[~~L TISS Item~~ table]]

** [[L_Pharm_Flowsheet table]]

** [[~~L TmpV2~~ table]]

** [[L_TISS Item table]] (in different file)

** [[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:

{| class="wikitable sortable"

Line 31:

Line 34:

| [[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]]

Line 45:

Line 48:

| [[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 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.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_Dxs ~~table~~ table]] ====

=== [[L_Dxs table]] ===

[[L_Como table]] links to [[~~S AllDiagnoses~~]] ~~table~~ as

[[L_Como table]] links to [[s_AllDiagnoses table]] as

* L_Dxs table.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]]

=== ~~Created/Derived Data~~ ===

== Related articles ==

* [[Created variables common table]]

* [[Created variables CC table]]

[[Category:Data structure | *]]

@@ 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.accdb]]/[[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_ICD10 table]]
-** [[L Dxs table]]
+** [[L_CCI_Picklist table]]
-** [[L_Labs_Flowsheet table table table]]
+** [[L_CCI_Component table]]
-** [[L_Pharm_Flowsheet table table]]
+** [[L_Labs_Flowsheet table]]
-** [[L TISS Item table]]
+** [[L_Pharm_Flowsheet table]]
-** [[L TmpV2 table]]
+** [[L_TISS Item table]] (in different file)
+** [[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:
 {| class="wikitable sortable"
@@ Line 31: / Line 34: @@
 | [[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]]
@@ Line 45: / Line 48: @@
 | [[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 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.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_Dxs table table]] ====
+=== [[L_Dxs table]] ===
-[[L_Como table]] links to [[S AllDiagnoses]] table as
+[[L_Como table]] links to [[s_AllDiagnoses table]] as
-* L_Dxs table.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]]
-=== Created/Derived Data ===
+== Related articles ==
-* [[Created variables common table]]
+{{Related Articles}}
-* [[Created variables CC table]]
 [[Category:Data structure | *]]