I have the following Schema design. For simplicity of this example, I am just using Patient ID as the key to illustrate my question.
In my fact table, I have some facts that relate to patients and I want to know which patients had a Liver or Heart biopsy performed. These dimension tables relate as a whole to the patient and not necessarily to any individual fact row.
However, in my operating system, you have to actually enter information in the Liver and Heart sections for it to record data in the database's tables. So, for example, if the Heart doesn't need information put into the system, people will simply skip entering in info.
So you can see patient 2 is not even in my liver dimension table, and patient 1 is not in the heart.
I have a few questions
Can 2 or more dimension tables use the same fact table FK? Can I use Patient ID to link both dimension tables to the fact table
In a Star Schema, would it be better to simply join the heart/liver dimension tables together into one? I understand that this is totally my decision, but I am curious if there is a rule or best practice where if the same key can link 2 different dimension table together, they really should be a single dimension table
If I do keep them separate and I want to pull data on how many patients had a Liver Biopsy done, I would get 2 Yes, 1 No and 1 Blank (Patient 2). How would I fix this without putting every patient in the Liver Dimension table and having every row except Biopsy be NULL?
