Dimensional modeling on HospitalStay

View previous topic View next topic Go down

Dimensional modeling on HospitalStay

Post  jon on Tue Jun 01, 2010 3:44 am

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.

jon

Posts: 11
Join date: 2010-05-09

View user profile

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

Permissions in this forum:
You cannot reply to topics in this forum