Related Facts?

Hi

I am trying to model some healthcare data and I am really struggling to understand how best to structure some of the tables.

Each patient within our system will have one or more case records, containing start and end date, current status etc for the different teams within the hospital then within each case the patient can have multiple contact records or even inpatient episode records.

I will have a patient dimension table and a fact table for each contact and a fact table for each inpatient episode with a FK reference in each table to the patient but I am unsure how I can store the case records as they relate to the patient and the contacts/episodes. Should they be a separate fact table and if so how can I show the relationship to the contacts

Any guidance would be really appreciated

Thanks

What exactly is a case record? Is it a metric at a higher grain than Contact Record and Inpatient Episode or possibly a dimension?

A case is an episode of treatment with a particular team. The episode would have a start and end date, a link to the patient, a link to the team, an outcome and a current status. The individual assessments, appointments or inpatient stays would then be linked to the case.

Thanks

OK. I would look at combining the Contact Record and Inpatient Episode into a single fact as they appear to be at the same grain. Case Record looks like it is at a higher grain which requires another fact table. You can aggregate up the Contact Record and Inpatient Episode counts to the higher level fact (which would probably satisfy the majority of your queries).

Thanks for your reply.

Would I then add a relationship from the contacts fact to the case fact?

No. Query optimizers that are aggregate aware should be able to choose the correct fact table based on predicates. Aggregate facts do not have relationships to their detail facts.

I thought both are the same.


______________________________
The Ideal Answer For type of life insurance policy & affordable term life insurance rate That You Simply Could Find Out About At this time