Details
Description
There is difference between what's documented in the CRC Design Document (p 42) and the actual DB constraints.
According to this document, the PK includes:
Encounter number
Patient number
Concept code
Start date
Modifier code
Observer code
However, in reality[1], it consists of:
ENCOUNTER_NUM,
CONCEPT_CD,
PROVIDER_ID,
START_DATE,
MODIFIER_CD,
INSTANCE_NUM
I think it would make sense to at least add the patient_num to the pk. This could be necessary when the encounter and provider are unknown and point to a default row in the dimension tables. I don't have a strong preference on how to deal with the remaining differences.
[1]:
NewInstall/Crcdata/scripts/crc_create_datamart_oracle.sql:140: CONSTRAINT OBSERVATION_FACT_PK PRIMARY KEY(ENCOUNTER_NUM,CONCEPT_CD,PROVIDER_ID,START_DATE,MODIFIER_CD,INSTANCE_NUM)
NewInstall/Crcdata/scripts/crc_create_datamart_sqlserver.sql:138: CONSTRAINT OBSERVATION_FACT_PK PRIMARY KEY nonclustered (ENCOUNTER_NUM, CONCEPT_CD, PROVIDER_ID, START_DATE, MODIFIER_CD, INSTANCE_NUM)
Upgrade/Crcdata/scripts/crc_create_datamart_oracle.sql:21:drop CONSTRAINT OBSERVATION_FACT_PK
Upgrade/Crcdata/scripts/crc_create_datamart_oracle.sql:29:add CONSTRAINT OBSERVATION_FACT_PK PRIMARY KEY (ENCOUNTER_NUM,CONCEPT_CD,PROVIDER_ID,START_DATE,MODIFIER_CD,INSTANCE_NUM)
Upgrade/Crcdata/scripts/crc_create_datamart_sqlserver.sql:41:drop CONSTRAINT OBSERVATION_FACT_PK
Upgrade/Crcdata/scripts/crc_create_datamart_sqlserver.sql:82:add CONSTRAINT OBSERVATION_FACT_PK PRIMARY KEY (ENCOUNTER_NUM,CONCEPT_CD,PROVIDER_ID,START_DATE,MODIFIER_CD,INSTANCE_NUM)
According to this document, the PK includes:
Encounter number
Patient number
Concept code
Start date
Modifier code
Observer code
However, in reality[1], it consists of:
ENCOUNTER_NUM,
CONCEPT_CD,
PROVIDER_ID,
START_DATE,
MODIFIER_CD,
INSTANCE_NUM
I think it would make sense to at least add the patient_num to the pk. This could be necessary when the encounter and provider are unknown and point to a default row in the dimension tables. I don't have a strong preference on how to deal with the remaining differences.
[1]:
NewInstall/Crcdata/scripts/crc_create_datamart_oracle.sql:140: CONSTRAINT OBSERVATION_FACT_PK PRIMARY KEY(ENCOUNTER_NUM,CONCEPT_CD,PROVIDER_ID,START_DATE,MODIFIER_CD,INSTANCE_NUM)
NewInstall/Crcdata/scripts/crc_create_datamart_sqlserver.sql:138: CONSTRAINT OBSERVATION_FACT_PK PRIMARY KEY nonclustered (ENCOUNTER_NUM, CONCEPT_CD, PROVIDER_ID, START_DATE, MODIFIER_CD, INSTANCE_NUM)
Upgrade/Crcdata/scripts/crc_create_datamart_oracle.sql:21:drop CONSTRAINT OBSERVATION_FACT_PK
Upgrade/Crcdata/scripts/crc_create_datamart_oracle.sql:29:add CONSTRAINT OBSERVATION_FACT_PK PRIMARY KEY (ENCOUNTER_NUM,CONCEPT_CD,PROVIDER_ID,START_DATE,MODIFIER_CD,INSTANCE_NUM)
Upgrade/Crcdata/scripts/crc_create_datamart_sqlserver.sql:41:drop CONSTRAINT OBSERVATION_FACT_PK
Upgrade/Crcdata/scripts/crc_create_datamart_sqlserver.sql:82:add CONSTRAINT OBSERVATION_FACT_PK PRIMARY KEY (ENCOUNTER_NUM,CONCEPT_CD,PROVIDER_ID,START_DATE,MODIFIER_CD,INSTANCE_NUM)