Take the 2-minute tour ×
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. It's 100% free, no registration required.

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 stackoverflow.com 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
add comment

2 Answers 2

up vote 6 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
add comment

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  
+1 For Foreign Key nulls get in the way and make queries complex and error prone. –  Cape Cod Gunny Jun 28 '11 at 13:56
add comment

Your Answer

 
discard

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.