Dimensional modeling on HospitalStay
Page 1 of 1
Dimensional modeling on HospitalStay
a different question on The Data Warehouse Toolkit, 2nd Ed. The complete guide to Dimensional Modeling.
In Chapter 13 page 265
"In a hospital stay situation, however, the diagnosis group probably should be unique to the patient because it is going to evolve over time as type 2 slowly changing dimension. In this case we would supplement the bridge table with two date stamps to capture begin and end dates."
My question is which one is the correct table for hospital stay:
create table PatientWithHospitalStayDiagnosisGroupBridge1
(
DiagnosisGroupKey int identity primary key
,DiagnosisKey int NOT NULL
,BeginDateKey int NOT NULL
,EndDateKey int NULL
,WeightFactor tinyint NULL
)
create table PatientWithHospitalStayDiagnosisGroupBridge2
(
DiagnosisGroupKey int identity primary key
,DiagnosisKey int NOT NULL
,PatientKey int NOT NULL -- with patient key
,BeginDateKey int NOT NULL
,EndDateKey int NULL
,WeightFactor tinyint NULL
)
if it is the first one, PatientWithHospitalStayDiagnosisGroupBridge1 , how to express the part "the diagnosis group probably should be unique to the patient"?
is relationship between Diagnosis Group to Patient a one to one relationship? (DiagGroup +-+ Patient)
is relationship between Diagnosis to Patient a many to one relationship? (Diag >-+ Patient)
is relationship between Diagnosis Group to HealthCareBillingLineItemFact a one to one relationship?
To me, it is almost like each HealthCareBillingLineItemFact has one Master Billing ID for that patient for that "visit" and that visit has one Diagnosis Group Key associate to it.
In Chapter 13 page 265
"In a hospital stay situation, however, the diagnosis group probably should be unique to the patient because it is going to evolve over time as type 2 slowly changing dimension. In this case we would supplement the bridge table with two date stamps to capture begin and end dates."
My question is which one is the correct table for hospital stay:
create table PatientWithHospitalStayDiagnosisGroupBridge1
(
DiagnosisGroupKey int identity primary key
,DiagnosisKey int NOT NULL
,BeginDateKey int NOT NULL
,EndDateKey int NULL
,WeightFactor tinyint NULL
)
create table PatientWithHospitalStayDiagnosisGroupBridge2
(
DiagnosisGroupKey int identity primary key
,DiagnosisKey int NOT NULL
,PatientKey int NOT NULL -- with patient key
,BeginDateKey int NOT NULL
,EndDateKey int NULL
,WeightFactor tinyint NULL
)
if it is the first one, PatientWithHospitalStayDiagnosisGroupBridge1 , how to express the part "the diagnosis group probably should be unique to the patient"?
is relationship between Diagnosis Group to Patient a one to one relationship? (DiagGroup +-+ Patient)
is relationship between Diagnosis to Patient a many to one relationship? (Diag >-+ Patient)
is relationship between Diagnosis Group to HealthCareBillingLineItemFact a one to one relationship?
To me, it is almost like each HealthCareBillingLineItemFact has one Master Billing ID for that patient for that "visit" and that visit has one Diagnosis Group Key associate to it.
jon- Posts : 11
Join date : 2010-05-10
Similar topics
» Dimensional Modeling-Checklist
» Need for a dimensional modeling tool?
» Dimensional Modeling Certification
» Advice on Dimensional Modeling
» Dimensional Modeling design
» Need for a dimensional modeling tool?
» Dimensional Modeling Certification
» Advice on Dimensional Modeling
» Dimensional Modeling design
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum