I'm trying to learn about dimensional models and star schemas. Say I have a Sales fact table recording the total sales at retail stores, with, say, four dimensions, Date, Customers, Stores, and Promotions (as in sales promotions like coupons). (Pseudo-schema below).
I really like the idea of prepopulating the Date dimension with attributes like day of the week, so that you can easily filter the facts by seemingly complex conditions like "saturdays in the second quarter for the past 10 years".
Now, say I want the Promotion dimension to have Start_Date and End_Date attributes which denote the start and end of the promotion. How do I do this? I thought about it and can come up with three less than ideal solutions:
1) Make Start_Date and End_Date regular, atomic attributes (either ISO8601 strings or the DB's datetime object) and only allow limitted filtering.
2) Make Start_Date and End_Date foreign keys to the Date table, breaking the star schema, but allowing the same filtering capabilities as the full Date dimension.
3) Include attributes like Start_Date_Day_of_Week, Start_Date_Quarter, maintaining the star schema but increasing the dimension size and essentially duplicating the structure of the Date dimension in the Promotions dimension.
Sales Table
-----------
Date key (FK to Dates table)
Promotion key (FK to Promotions)
Customer key (FK to Customers table)
Store key (FK to Stores)
Sales Amount
Date Table
----------
Date Key (PK)
Month
Day of week
Day of month
Day of Quarter
Day of year
Holiday?
Quarter
Day since Epoch
Month since Epoch
etc...
Promotion Table
---------------
Promotion Key (PK)
Type (Coupon code, 2-for-1 sale, 50% off, etc)
Start_Date
End_Date
Customer Table
--------------
Customer Key (PK)
Name
Age
Sex
etc...
Stores Table
------------
Store Key (PK)
Address
City State
Zip
etc...