dan-579597 (10/1/2009)
Thinking about where to put the dates:I'm not sure if this is better or just over-engineering, but what about a PromotionInstance (PI) table between Promotion (P) and PromotionProduct (PP) with the start and end dates in PI.
Either give PI its own ID, e.g. PromotionInstanceID, that can be part of the PP PK (instead of the PromotionID), or have the Start and End dates be part of the PI PK and have the PP table look as it does in the article.
The advantages I see are small, but here they are:
- You can have a recurring promotion described in P ("Semi-Annual Sale"), then have the specific occurrence's details in PI ("Fall 2009 Back To School Sale") and the products are strongly coupled to a real-life event.
- A better level of normal form by having the PP dates either as FK to PI or by eliminating them from PP in favor of a PI ID. This makes me more comfortable in with data integrity, though I can see the argument that it's safe enough if the dates in PP are system-managed.
Promotions that only ever occur once would just have one instance, of course.
Would you ever need to treat the multiple occurrences of a sale like your 'semi-annual sale' as one object? If not, I'd think this is overkill, as each occurrence would be treated and analyzed as a separate animal.
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."