October 12, 2009 at 11:19 pm
Unfortunately, the article glossed over quite a bit. The Devil in these details is a much bigger beast. For example, including the effective dates as part of the primary key will not prevent duplicates as others have mentioned. Granted, we cannot see check constraints or triggers in his design, but one could image data such as:
PromotionId, ProductId, Start, End
1, 1, 1/1/2009, 1/31/2009 5:00 PM
1, 1, 1/1/2009, 1/31/2009 11:59 PM
1, 1, 1/2/2009, 1/31/2009 11:59 PM
I have dealt with temporal designs in the past and in one case a design that required *two* sets of dates on numerous data elements. The single greatest headache is that adding temporal fields will break normal data integrity rules such as the intent of primary keys as above mentioned. In order to prevent having two active promotions for the same product and promotion, you must use triggers to enforce data integrity rules which account for overlap.
A temporal design most definitely is the right fit in certain circumstances but it should be clear that it must be a decision in which the stakeholders account for the additional development cost. That cost rears its head in every query (and thus report) against the data.
Temporal designs can get quite complicated fast. For example, I can see a requirement for an effective date range for both the promotion and the product's membership to the promotion. "This promotion is only for June. Product X will not be available as part of that promotion until June 15 due to supplier issues." etc.
October 12, 2009 at 11:26 pm
Jason Whitney (10/1/2009)
I have run into this before, and the article gives the first half of the solution. The second part is what I can't figure out; how to avoid overlapping time-frames.The supplied solution doesn't enforce data integrity for overlapping dates. For the illustration given it may not need to, but for many applications these date ranges must be mutually exclusive. For example a patient comes in to the hospital as an outpatient (O) and has a complication and gets admitted as an inpatient (I). The data is temporal, but the dates/times cannot overlap. How would I model this?
The only solution I have been able to come up with is a new data type for interval data. (Hopefully we will see this in the next version of SQL) I tried a CLR, but I was never able to get it to work quite right. My database has failed me! :crazy:
The safest solution is to use triggers. The next safest solution is to use stored procs. With triggers you would look for an overlap in the inserted table in comparison to the existing table. Something like:
If Exists(Select *
From dbo.PromotedProducts As PP
Join inserted As I
On I.PromotionId = PP.PromotionId
And I.ProductId = PP.ProductId
And I.Start <= PP.EndDate
And I.EndDate >= PP.StartDate )
Raiserror(...
Viewing 2 posts - 46 through 46 (of 46 total)
You must be logged in to reply to this topic. Login to reply