[CORE-81] No PATIENT_NUM in OBSERVATION_FACT_PK (conflict with documentation) Created: 15/Oct/13 Updated: 05/Jun/14 Resolved: 08/May/14 |
|
| Status: | Closed |
| Project: | i2b2 Core Software |
| Component/s: | CRC Cell, Data |
| Affects Version/s: | 1.6.08 |
| Fix Version/s: | 1.7.00 |
| Type: | Bug | Priority: | Major |
| Reporter: | Boris De Vloed | Assignee: | Janice Donahoe |
| Resolution: | Fixed | Votes: | 0 |
| Labels: | None | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Environment: | Both SQL Server and Oracle, OS independent. | ||
| Rank: | 0|i000pj: |
| Participant/s: |
| 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) |
| Comments |
| Comment by Janice Donahoe [ 08/May/14 ] |
| This issue was fixed in the 1.7.00 release (r2623). The install scripts have been updated to include the PATIENT_NUM column in the OBSERVATION_FACT_PK. |
| Comment by Janice Donahoe [ 08/May/14 ] |
| Issue fixed in release 1.7.00 (r2623) |