Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

share|improve this question

migrated from Jun 27 '11 at 16:54

This question came from our site for professional and enthusiast programmers.

possibly a serverfault question? – bluefeet Jun 27 '11 at 16:06
up vote 8 down vote accepted

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.

share|improve this answer
Thanks for your answer!!! – akotian Jun 27 '11 at 17:26

Don't put nulls in the Warehouse or in the Marts.

The warehouse should be well normalized (at least BCNF) and therefore should exclude nulls. Nulls might be preserved in staging tables if they exist in data sources but they shouldn't be needed in the warehouse itself.

Marts should be designed to support presentation tools and user queries. Nulls just get in the way of those things because they are never displayed and they make user queries more complex and error-prone - especially in foreign key columns which are frequently subject to joins.

share|improve this answer
Thanks for replying! – akotian Jun 27 '11 at 17:27
+1 For Foreign Key nulls get in the way and make queries complex and error prone. – Michael Riley - AKA Gunny Jun 28 '11 at 13:56
I agree, but for the reason cited by Brown: it's very valuable to have explicit synthetic records for the reason the field would otherwise be NULL. NULL tells users nothing; "Value could not be parsed", "Field left blank", or "No account executive assigned yet" is useful. – Jon of All Trades Aug 11 '14 at 19:26

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.