I am new to data mart design and need to clear a few concepts.
I have read up a bit on dimension modelling where I see that fact tables store foreign key references to dimension tables.
Now suppose I have a phonenumber dimension table and a phone_extension dimension table. (These tables have different details because of which I cannot combine them)
As I understand both these dimension tables will have integer primary keys for better performance, and the fact table will have its own integer primary key and also store foreign key references to these dimension tables.
But suppose I have a situation that not all phone numbers have a phone_extension related to them. ( some phone numbers need not have an extension)
For phone numbers that have an extension, the fact table would have foreign key references to both the dimension tables, but how do I capture the situation where there are only phone numbers and no extension to them (and vice versa i.e extension with no phonenumbers)?
Should I capture such information with the phonenumber FK in the fact table having a value and phone_extension foreign key null?? Or are such non related objects not recorded in fact tables?
Also I need to generate report of this data mart. So do I start by querying the fact table and retrieving the dimension key values or report straight from the dimension table?
Thanks for your time in reading this!!
Appreciate any help!!