Surrogate key for date dimension?

Posted by Navin on Stack Overflow See other posts from Stack Overflow or by Navin
Published on 2012-08-31T04:17:17Z Indexed on 2012/08/31 9:38 UTC
Read the original article Hit count: 282

There are 2 school of thoughts :

  1. Use surrogate key preferbly in the format of YYYYMMDD as this will always be sequential.

  2. Eliminate Date dimension surrogate key and use actual date instead.

My Questions to experts on dimension modeling are :

1> Which design would you prefer and why ?

2> How should we handle unknown values in each of the cases, Can we simply place 
   NULL in Fact table for unknown dates as Foreign Key can be NULL (if no why)?

3> If we need to partition fact table on date column ,how would we achieve that 
   in case 1.

I am inclined towards using actual date and using NULL to represent UNKNOWN dates in fact table , as date related validation on fact can be done without need to look in to dimension table.

© Stack Overflow or respective owner

Related posts about Oracle

Related posts about database-design