How would you model this simple scenario ?

Hi Guys,
Need your help to figure out this simple scenario :

Have three tables related in the following way in 3NF

DESIGNATOR >-- Patient --< claims

Patient
-------
PatientId
PatientName

Designator
----------
DesignatorId
Designator
StartDate
EndDate

Claims
------
ClaimId
PatientId
ClaimDate
ClaimAmount

There could be multiple designator for a patient during any period of time.

I need to convert this to dimensional model and need to answer the following question : How may claims were filed during a certain period with certain designator. Is there any other way to model this besides snowflaking off of patient ?

Thanks
RK

You could build a bridge table off of claim_fact to designator_dim.

designator <-- claim --> patient

Assumes at a point in time a patient has only one designator (which appears to be what your original model implies). The reference here would be the designator at the time of the claim.

You may also carry current designator as an attribute on the patient dimension as well.

BoxesAndLines wrote:You could build a bridge table off of claim_fact to designator_dim.

I agree with you, you can also implement a "primary" flag, so you can always query against the primary designator without breaking granularity.

Thanks for all your responses
If we use bridge table, can we consider Patient dim itself as a bridge table to get to designator. Why would we need a separate table to bridge to designator ?

Thanks
RK

Depends on the data. If there can only be one designator effective at any point in time, then you do not need a bridge table. In which case you end up with two dims and one fact.

Patient won't be the bridge table, you have to define a bridge table between Patient and Designator.

claims >-- Patient -- < PatDesBridge (patientId, DesignatorId, primaryFlag) >-- Designator

you might want also to implement a factless fact table with Patient and designator (with prob date) to define the ocurrences of which designators were associated with a patient in certaint point of time (check Kimball's example about the factless fact table for students).

RkvCosmos wrote:Thanks for all your responses
If we use bridge table, can we consider Patient dim itself as a bridge table to get to designator. Why would we need a separate table to bridge to designator ?

Thanks
RK

How do you have it modeled in 3NF? From what you had shown in the original post DOES NOT support multiple designators for a patient.

The thing is, many to many relationships are handled the same way in both a 3NF model and a dimensional model. You need an associative entity (aka bridge table) to handle the relationship.

In this case the relationship is between designator and patient, so, the bridge contains FKs to both. Where a 3NF model and dimensional model differ is that in a dimensional model the bridge does not sit between dimensions, but rather between a dimension and fact. In this case you would join the patient FK off the fact, through the bridge to the designator dimension.

Another way to deal with it is to ignore patient altogether and treat designator as a multi-valued dimension. The result would be a much smaller bridge table, but you would only have point in time values as it relates to the fact.

Boxes and Lines :
There could be multiple designators for a certain time period. In that case we will need some kind of bridge. We can create a separate bridge between fctClaims and Designator, I agree with that. But can we use Patient as the bridge table instead of creating a separate bridge table. Are there any drawback to modelling it this way ?

aperMag :
Thanks for the response. I am trying to figure out a way to relate claims to the designator. The question I am trying to answer is how many claims are there for certain designator and certain time period. So one approach is to create bridge
fctClaims >-- brdg --< designator

But the designator is for the patients. So why cant we have
fctClaims >-- Patient --< designator.

Thanks
RK

if "claims" has to be the granularity of fact table then you can use the bridge table and do a count distinct of you claim_sk by using

FactClaims >--- Patient ---< Bridge >--- Designator

In this way, as 1 patient can have N designators and 1 designator can be with N patients, you will get an M to N relationship with that bridge.
Then, when joining patient with facts, the "claims" grain will be "exploded" (several lines for the same claim as you have multiple designators). That's why you have to use COUNT DISTINCT (claim_sk).

If you can break "claims" granularity, then add the dimension to the fact table and your granularity would be "Designator & Claim" and apply the same count distinct.

Another option is to focus in just the "primary" designator, but I don't know if you have that role in your business.

None of them are the best, but I would use the first one.

You don't need a bridge table to answer this question, How may claims were filed during a certain period with certain designator. You may need a bridge table to answer other questions.

BoxesAndLines wrote:You don't need a bridge table to answer this question, How may claims were filed during a certain period with certain designator. You may need a bridge table to answer other questions.

That is true, but you could break the "claims" granularity (if you have amounts in that fact table, then you won't be able to calculate the sum of them).

So one approach is to create bridge
fctClaims >-- brdg --< designator

But the designator is for the patients. So why cant we have
fctClaims >-- Patient --< designator.

Because, as you said, a patient may have more than one designator. There must be a table that contains a patient/designator relationship to allow for multiple occurances.

If a patient will never have more than one designator, you might as well store it as a attribute in the patient dimension and be done with it.

Since the bridge contains both the patient key and the designator key you can use it to join between patient and designator dimensions (without a fact table in the query). However, the general use case is to use the bridge between fact and dimension. It is a more efficient join for star schema and works when a query does not require patient attributes.

Thanks all for you responses.
ngalemmo:
You are correct, I did miss the table that show many to many relationships between patient and designators.
So its Patient --< PatientDesignator >-- Designator

PatientDesignator also has dates.
Patient Designator
---------------------
PatientId
DesignatorId
StartDate
EndDate

If I am creating a bridge table between Claims and Designator, can I add dates to it ?

Thanks
RK

I guess you would have to. If the patient/designation relationship changes over time, it would be a reasonable way to reflect it. However, it raises another question... from a claim point of view, do you need the designations at the time of the claim or do you need to associate the claim with designations at any point in time? In the latter case, the patient/designator bridge with dates make sense. But if the former is the case then treating designator as an independed multi-valued dimension is the better way to go.

That makes sense. In our case I will have to take the bridge route. Thanks ngalemmo, boxes and lines and apermag for your help.

TK