Overlapping Dates in a dimension

View previous topic View next topic Go down

Overlapping Dates in a dimension

Post  MarkWojo128 on Wed Jun 17, 2009 12:49 pm

Hi,
I am wondering if someone could advise me on how to deal with this design problem. I have a promotion dimension keyed to a sales fact table. Initially, we thought that only one promotion could be active at one time for a sale, but it is actually the case that multiple promotions can be active in overlapping time frames. For example:

Promo 1 - Start DT 5/11/2009 End DT 5/22/2009
Promo 2 - Start DT 5/18/2009 End DT 5/28/2009

So, if I have a sale that occurs between 5/18 and 5/22, both promotions would apply. We thought about creating a bridge table to link the fact and dimension, but the complication is that there appears to be no good way to link the promotions to the bridge table. The record would be unique by the item, customer and date range. How would we know that the two promotions above should be linked to the same bridge record? Additionally, there would be a time when only promo 1 was in effect and only promo 2. A bridge table would have to account for these 3 instances. This would create a many to many relationship between the bridge table and dimension and we would be no better off then when we started. Can the bridge table appoach work or is there a wholely better approach.
Thanks for any thoughts,
Mark

MarkWojo128

Posts : 1
Join date : 2009-06-17

View user profile

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum