wiki

Changes

MDCCD Data Warehouse Data Dictionary (view source)

Revision as of 15:10, 28 August 2024

1,910 bytes added ,  15:10, 28 August 2024
no edit summary
Line 1: Line 1:  +
{{Delete|reason=Moved to internal Health Canada wiki. Internal database structure should not be public.}}
 
== Overview ==
 
== Overview ==
 
This page provides a data dictionary for each table that is held within the [[MDCCD Data Warehouse]].
 
This page provides a data dictionary for each table that is held within the [[MDCCD Data Warehouse]].
Line 1,380: Line 1,381:  
[[MDCCD Data Warehouse|Return to Home Page.]]
 
[[MDCCD Data Warehouse|Return to Home Page.]]
   −
This table holds information that categorizes or "tags" an inspection.
+
This table holds information about the observations for an inspection.
 
{| class="wikitable sortable mw-collapsible"
 
{| class="wikitable sortable mw-collapsible"
 
|+
 
|+
Line 1,417: Line 1,418:  
|5c6afa3c-3f8a-4641-8da4-9588ec7769d7
 
|5c6afa3c-3f8a-4641-8da4-9588ec7769d7
 
|This is the unique record identifier for the parent inspection.
 
|This is the unique record identifier for the parent inspection.
−
|
+
|Use to join with MD_INS_CASE.INSP_ID.
 
|-
 
|-
 
|INSP_NUM
 
|INSP_NUM
Line 1,623: Line 1,624:  
|}
 
|}
   −
 
+
===MD_INS_INS_CASE_WORKFLOW===
−
=== MD_INS_CASE_WORKFLOW ===
   
[[MDCCD Data Warehouse|Return to Home Page.]]
 
[[MDCCD Data Warehouse|Return to Home Page.]]
   −
This table holds information that categorizes or "tags" an inspection.
+
This table holds information about the workflow of the inspection.
 
{| class="wikitable sortable mw-collapsible"
 
{| class="wikitable sortable mw-collapsible"
 
|+
 
|+
−
! scope="col" style="width: 350px;" |Column Name
+
! scope="col" style="width: 350px;" | Column Name
 
! scope="col" style="width: 100px;" |DataType
 
! scope="col" style="width: 100px;" |DataType
−
! scope="col" style="width: 50px;" |Key Type
+
! scope="col" style="width: 50px;" | Key Type
 
! scope="col" style="width: 50px;" |Nullable
 
! scope="col" style="width: 50px;" |Nullable
 
! scope="col" style="width: 50px;" |Data Default
 
! scope="col" style="width: 50px;" |Data Default
Line 1,639: Line 1,639:  
! scope="col" style="width: 350px;" |Comments
 
! scope="col" style="width: 350px;" |Comments
 
|-
 
|-
 +
|WF_ID
 +
|VARCHAR2(36 CHAR)
 +
|Primary
 +
|No
 +
|NULL
 +
|5c6afa3c-3f8a-4641-8da4-9588ec7769d7
 +
|This is the unique record identifier for the workflow.
 
|
 
|
 +
|-
 +
|WF_NAME
 +
|VARCHAR2(50 BYTE)
 
|
 
|
 +
|Yes
 +
|NULL
 +
|Inspection Business Process
 +
|This is the name of the workflow.
 
|
 
|
 +
|-
 +
|INSP_ID
 +
|VARCHAR2(36 CHAR)
 +
|Foreign
 +
|No
 +
|NULL
 +
|5c6afa3c-3f8a-4641-8da4-9588ec7769d7
 +
|This is the unique record identifier for the parent inspection.
 +
|Use to join with MD_INS_CASE.INSP_ID.
 +
|-
 +
|INSP_NUM
 +
|VARCHAR2(200 CHAR)
 
|
 
|
 +
|No
 +
|NULL
 +
|00070-MDCCD HEALTHCARE INC
 +
|This is the parent inspection number.
 
|
 
|
 +
|-
 +
|ACTIVESTAGEID
 +
|VARCHAR2(36 CHAR)
 
|
 
|
 +
|No
 +
|NULL
 +
|5c6afa3c-3f8a-4641-8da4-9588ec7769d7
 +
|This is the unique record identifier for the current stage of the workflow.
 
|
 
|
 
|-
 
|-
 +
|STAGE_NAME_EN
 +
|VARCHAR2(50 BYTE)
 
|
 
|
 +
|Yes
 +
|NULL
 +
|Start Of Inspection
 +
|This is the name of the current workflow stage in English.
 
|
 
|
 +
|-
 +
|STAGE_NAME_FR
 +
|VARCHAR2(50 BYTE)
 
|
 
|
 +
|Yes
 +
|NULL
 +
|Début de l'inspection
 +
|This is the name of the current workflow stage in French.
 
|
 
|
 +
|-
 +
|STAGE_START_DATE
 +
|DATE
 
|
 
|
−
|
+
|Yes
 +
|NULL
 +
|23-11-09
 +
|This is the date that the current workflow stage was started.
 
|
 
|
 
|-
 
|-
 +
|WF_COMPLETE_DATE
 +
|DATE
 
|
 
|
 +
|Yes
 +
|NULL
 +
|23-11-09
 +
|This is the date the workflow was completed.
 
|
 
|
 +
|-
 +
|WF_DURATION
 +
|NUMBER(4,0)
 
|
 
|
 +
|Yes
 +
|NULL
 +
|42
 +
|This identifies how many days the workflow was active.
 
|
 
|
 +
|-
 +
|CREATEDBYNAME
 +
|VARCHAR2(100 CHAR)
 
|
 
|
 +
|Yes
 +
|NULL
 +
|John Smith
 +
|This identifies the resource that created the record.
 
|
 
|
 +
|-
 +
|CREATEDON
 +
|DATE
 
|
 
|
−
|
+
|Yes
 +
|NULL
 +
|22-08-26
 +
|This identifies the date that record was created.
 
|-
 
|-
 +
|MODIFIEDBYNAME
 +
|VARCHAR2(100 CHAR)
 
|
 
|
 +
|Yes
 +
|NULL
 +
|John Smith
 +
|This identifies the resource that last modified the record.
 
|
 
|
 +
|-
 +
|MODIFIEDON
 +
|DATE
 
|
 
|
 +
|Yes
 +
|NULL
 +
|22-08-26
 +
|This identifies the date that record was last modified.
 
|
 
|
−
|
+
|}
−
|
+
 
−
|
+
 
 +
===MD_INS_INS_IID===
 +
[[MDCCD Data Warehouse|Return to Home Page.]]
 +
 
 +
This table holds the Initial Inspection Deficiency information for an inspection that is posted on the DHPID.
 +
{| class="wikitable sortable mw-collapsible"
 +
|+
 +
! scope="col" style="width: 350px;" | Column Name
 +
! scope="col" style="width: 100px;" |DataType
 +
! scope="col" style="width: 50px;" | Key Type
 +
! scope="col" style="width: 50px;" |Nullable
 +
! scope="col" style="width: 50px;" |Data Default
 +
! scope="col" style="width: 500px;" |Example Data
 +
! scope="col" style="width: 350px;" |Description
 +
! scope="col" style="width: 350px;" |Comments
 
|-
 
|-
 
|
 
|
Line 1,688: Line 1,797:  
|
 
|
 
|-
 
|-
 +
|
 
|
 
|
 
|
 
|
Line 1,750: Line 1,860:  
|
 
|
 
|
 
|
−
|}
+
|
−
 
+
|-
−
 
+
|
−
===MD_INS_INS_IID===
+
|
−
[[MDCCD Data Warehouse|Return to Home Page.]]
+
|
−
 
+
|
−
This table holds information that categorizes or "tags" an inspection.
+
|
−
{| class="wikitable sortable mw-collapsible"
+
|
−
|+
+
|
−
! scope="col" style="width: 350px;" | Column Name
  −
! scope="col" style="width: 100px;" |DataType
  −
! scope="col" style="width: 50px;" | Key Type
  −
! scope="col" style="width: 50px;" |Nullable
  −
! scope="col" style="width: 50px;" |Data Default
  −
! scope="col" style="width: 500px;" |Example Data
  −
! scope="col" style="width: 350px;" |Description
  −
! scope="col" style="width: 350px;" |Comments
   
|-
 
|-
 
|
 
|
Line 1,790: Line 1,892:  
|
 
|
 
|
 
|
−
|
+
|}
−
|
+
 
 +
===MD_INS_INS_INSPECTORLIST===
 +
[[MDCCD Data Warehouse|Return to Home Page.]]
 +
 
 +
This table holds a list of inspectors within the system.
 +
{| class="wikitable sortable mw-collapsible"
 +
|+
 +
! scope="col" style="width: 350px;" | Column Name
 +
! scope="col" style="width: 100px;" |DataType
 +
! scope="col" style="width: 50px;" | Key Type
 +
! scope="col" style="width: 50px;" |Nullable
 +
! scope="col" style="width: 50px;" |Data Default
 +
! scope="col" style="width: 500px;" |Example Data
 +
! scope="col" style="width: 350px;" |Description
 +
! scope="col" style="width: 350px;" |Comments
 
|-
 
|-
 
|
 
|
Line 1,809: Line 1,925:  
|
 
|
 
|-
 
|-
 +
|
 
|
 
|
 
|
 
|
Line 1,881: Line 1,998:  
|}
 
|}
   −
===MD_INS_INS_INSPECTORLIST===
+
===MD_INS_INS_IRC_ENFORCEMENT===
 
[[MDCCD Data Warehouse|Return to Home Page.]]
 
[[MDCCD Data Warehouse|Return to Home Page.]]
   −
This table holds information that categorizes or "tags" an inspection.
+
This table holds the list of enforcement actions taken by Health Canada for the Inspection Report Card posted on the DHPID.
 
{| class="wikitable sortable mw-collapsible"
 
{| class="wikitable sortable mw-collapsible"
 
|+
 
|+
Line 1,913: Line 2,030:  
|-
 
|-
 
|
 
|
−
|
  −
|
  −
|
  −
|
  −
|
  −
|
  −
|
  −
|-
  −
|
  −
|
  −
|
  −
|
  −
|
  −
|
  −
|
  −
|-
   
|
 
|
 
|
 
|
Line 1,985: Line 2,086:  
|}
 
|}
   −
===MD_INS_INS_IRC_ENFORCEMENT===
+
===MD_INS_INS_IRC_MEASURES===
 
[[MDCCD Data Warehouse|Return to Home Page.]]
 
[[MDCCD Data Warehouse|Return to Home Page.]]
   −
This table holds information that categorizes or "tags" an inspection.
+
This table holds the list of corrective measures taken by Health Canada for the Inspection Report Card posted on the DHPID.
 
{| class="wikitable sortable mw-collapsible"
 
{| class="wikitable sortable mw-collapsible"
 
|+
 
|+
Line 2,073: Line 2,174:  
|}
 
|}
   −
===MD_INS_INS_IRC_MEASURES===
+
===MD_INS_INS_IRC_OBSERVATIONS===
 
[[MDCCD Data Warehouse|Return to Home Page.]]
 
[[MDCCD Data Warehouse|Return to Home Page.]]
   −
This table holds information that categorizes or "tags" an inspection.
+
This table holds the list of observations for the Inspection Report Card posted on the DHPID.
 
{| class="wikitable sortable mw-collapsible"
 
{| class="wikitable sortable mw-collapsible"
 
|+
 
|+
Line 2,152: Line 2,253:  
|
 
|
 
|
 
|
−
|-
  −
|
  −
|
  −
|
  −
|
  −
|
  −
|
  −
|}
  −
  −
===MD_INS_INS_IRC_OBSERVATIONS===
  −
[[MDCCD Data Warehouse|Return to Home Page.]]
  −
  −
This table holds information that categorizes or "tags" an inspection.
  −
{| class="wikitable sortable mw-collapsible"
  −
|+
  −
! scope="col" style="width: 350px;" | Column Name
  −
! scope="col" style="width: 100px;" |DataType
  −
! scope="col" style="width: 50px;" | Key Type
  −
! scope="col" style="width: 50px;" |Nullable
  −
! scope="col" style="width: 50px;" |Data Default
  −
! scope="col" style="width: 500px;" |Example Data
  −
! scope="col" style="width: 350px;" |Description
  −
! scope="col" style="width: 350px;" |Comments
   
|-
 
|-
 
|
 
|
Line 2,192: Line 2,270:  
|
 
|
 
|-
 
|-
−
|
   
|
 
|
 
|
 
|
Line 2,247: Line 2,324:  
|
 
|
 
|
 
|
−
|
+
|
−
|-
+
|-
−
|
+
|
−
|
+
|
−
|
+
|
−
|
+
|
−
|
+
|
−
|
+
|
−
|
+
|}
−
|-
  −
|
  −
|
  −
|
  −
|
  −
|
  −
|
  −
|
  −
|-
  −
|
  −
|
  −
|
  −
|
  −
|
  −
|
  −
|
  −
|-
  −
|
  −
|
  −
|
  −
|
  −
|
  −
|
  −
|
  −
|-
  −
|
  −
|
  −
|
  −
|
  −
|
  −
|
  −
|
  −
|-
  −
|
  −
|
  −
|
  −
|
  −
|
  −
|
  −
|
  −
|-
  −
|
  −
|
  −
|
  −
|
  −
|
  −
|
  −
|
  −
|-
  −
|
  −
|
  −
|
  −
|
  −
|
  −
|
  −
|
  −
|-
  −
|
  −
|
  −
|
  −
|
  −
|
  −
|
  −
|
  −
}
      
===MD_INS_INS_SITE===
 
===MD_INS_INS_SITE===
 
[[MDCCD Data Warehouse|Return to Home Page.]]
 
[[MDCCD Data Warehouse|Return to Home Page.]]
   −
This table holds information that categorizes or "tags" an inspection.
+
This table holds the site/company dictionary. This information is migrated from MDS.
 
{| class="wikitable sortable mw-collapsible"
 
{| class="wikitable sortable mw-collapsible"
 
|+
 
|+
Line 2,557: Line 2,569:  
[[MDCCD Data Warehouse|Return to Home Page.]]
 
[[MDCCD Data Warehouse|Return to Home Page.]]
   −
This table holds information that categorizes or "tags" an inspection.
+
This table holds the site/company contact listing. This information is partially migrated from MDS.
 
{| class="wikitable sortable mw-collapsible"
 
{| class="wikitable sortable mw-collapsible"
 
|+
 
|+
Line 2,741: Line 2,753:  
[[MDCCD Data Warehouse|Return to Home Page.]]
 
[[MDCCD Data Warehouse|Return to Home Page.]]
   −
This table holds information that categorizes or "tags" an inspection.
+
This table holds the establishment licence dictionary. This information is migrated from MDS.
 
{| class="wikitable sortable mw-collapsible"
 
{| class="wikitable sortable mw-collapsible"
 
|+
 
|+
Line 2,893: Line 2,905:  
[[MDCCD Data Warehouse|Return to Home Page.]]
 
[[MDCCD Data Warehouse|Return to Home Page.]]
   −
This table holds information that categorizes or "tags" an inspection.
+
This table holds the establishment licence activity dictionary. This information is migrated from MDS.
 
{| class="wikitable sortable mw-collapsible"
 
{| class="wikitable sortable mw-collapsible"
 
|+
 
|+
Jeff.tyndall
71

edits

Retrieved from "https://wiki.gccollab.ca/Special:MobileDiff/120661...124251"