Fact table foreign keys null


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!!

Best Answer

You can leave the FK to some dimension tables as NULL if those dimensions are not known or not applicable. You just have to remember to use outer joins when you do your reporting query.

Alternatively, some people create a "none" and/or an "n/a" dimension record for data mart dimensions and then populate fact table FKs to point at these rather than using NULLs. People who do this like this approach because they have an aversion to outer joins.

People who use NULL FKs in fact tables usually have an aversion to people who have an version to outer joins. ;) (in other words, this is a stylistic issue which can generate religious wars)

I say do whichever you prefer, but pick one approach and stick to it fervently.