Healthcare Semantic Model

I am asking to map MRN -> condition_occurrence.person_id -> visit_occurrence.visit_occurrence_id -> Care_site.care_site_name

but I also need MRN -> procedure_occurrence.person_id -> visit_occurrence.visit_occurrence_id -> Care_site.care_site_name

doesn't that defeat the purpose of the de-identification ?

Here are some guidelines to follow when designing a star schema, especially since you are dealing with healthcare data:

1. Your Fact table(s) should be normalized

2. Your Dimension tables should be de-normalized

Given the nature of healthcare data, I would almost say you absolutely need a EAV style Fact table. Relational database purists pooh-pooh EAV, but with the proper knowledge, you will come to really appreciate just how versatile they are. The number of attribute columns will be dictated by the number of Dimension tables. See below.

How many Dimension tables should you have? Six, a dozen? Well, you need to consider how users will want to slice the data and how heirachical those slices are.

The short rule is you need a Dimension table for each slicer e.g. Date, Product, Geography, etc, but you don't want seperate Dimension tables for Product Category and Product Subcategory because they live in the same slicer as a hierarchy.

The most intuitive example is a Date dimension table which has any number of hierarchies from day, week, month, quarter, etc. You de-normalize all that hierarchy into a single Date dimension table. The goal is to do the same for all your other dimension tables.

Now, it sounds like you may be dealing with many-to-many relationships (MMR) between your tables. In some contexts this is unavoidable, and knowing how to properly design a start schema for them is really important. The topic is far too complex to outline over a forum. My suggestion is you acquire a copy of The Definitive Guide to DAX. They have an entire chapter dedicated to MMRs and the various techniques available.

HTH