Surrogate key for date dimension?
- by Navin
There are 2 school of thoughts :
Use surrogate key preferbly in the format of YYYYMMDD as this will always
be sequential.
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.
