Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Effective Dating Series Part I - The Problem


Effective Dating Series Part I - The Problem

Author
Message
scott mcnitt
scott mcnitt
Valued Member
Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)

Group: General Forum Members
Points: 60 Visits: 432
You said "heinous"
Hehe
jcrawf02
jcrawf02
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1610 Visits: 19324
Mod -1, how is this article supposed to help me get a date?!

---------------------------------------------------------
How best to post your question
How to post performance problems
Tally Table:What it is and how it replaces a loop

"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."
Richard Gardner-291039
Richard Gardner-291039
SSC Veteran
SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)

Group: General Forum Members
Points: 265 Visits: 421
jcrawf02 (10/1/2009)
Mod -1, how is this article supposed to help me get a date?!


Read the title - "Part 1 - The Problem". It's a demonstration - you read the article and commented on an SQL forum. Case in point. Hopefully next week there'll be a cure for this kind of behaviour, or at least how to effectively hide it from the opposite sex.:-P
losreed
losreed
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 31
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.
jcrawf02
jcrawf02
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1610 Visits: 19324
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
How to post performance problems
Tally Table:What it is and how it replaces a loop

"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."
GSquared
GSquared
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16913 Visits: 9729
ddunn-1115368 (10/1/2009)
Great aticle! I've been trying to get this concept across to the developers of an accounting package that I work with. Managing changes in price lists (never mind promotional items) can be a bear. This process would simplify so many things for the end users. Being able to import "next week's price list" would be a great feature.

I would also consider using the date associated with the document header to locate these promo items instead of a system date. There will be cases where you have to back date a transaction and using the document's date vs. the system date will allow that.


I used the Placed (datetime) column of the Orders table to join to the pricing data in effect at the time the order was placed. That way, if you have to print out the order form a year later, you don't get current pricing, you get the price the order actually dealt with, which is important.

I also had a nullable "ContractID" column in the pricing tables that allowed customers to have specific contracts that gave them their own pricing structure, since that was important to the business. That data was also stored in the Orders table.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Richard Gardner-291039
Richard Gardner-291039
SSC Veteran
SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)

Group: General Forum Members
Points: 265 Visits: 421

As to the person wondering about discount rates, there are pros and cons to that. The pro, as mentioned, is not maintaining a separate list of hard prices. The immediately obvious con is figuring out later, say 6-mos down the road, when products prices' may have changed, just how much that product cost under that promotion.



You're talking about a different data set, that information is in your sales ledger.

I'd say it adds a lot to the flexibility, you can then treat the discount rate as a "what if" scenario - granted you'd have to create a view onto the data which works out the actual selling price after discount, then join that view to your reports (not so difficult), but think about sales forecasting and margin calculations (remember margin is a product of COGS, NOT current selling price, which to mind mind is irrelevant in this context), you'd have hard and fast historical data about what sort of discount generates what kind of extra demand pretty simply available - then you could start forecasting with different discount rates and tailor the promotions accordingly.

You can do this with the existing model, granted, but it makes it much more difficult. Also the existing model doesn't lend itself well to fast setup, personally I'd be looking into categorising products (if only by margin) so they can be treated en-masse in "promotional groups"
Nadrek
Nadrek
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1278 Visits: 2698
Unfortunately, with SQL and temporal databases, you appear to have missed one of the most difficult aspects of generalized date based fields: how to be both efficient and prevent bad data from getting in.

With most Start date + End date designs, you can trivially end up with multiple simultaneously overlapping date ranges, and thus, in your example... which promotion is really in effect? Are all of them in effect? Does one or more override others?
I.e. if we usually end up with (in your example) promotions on the same products that have date ranges of 8/1 to 8/15, 8/2 to 8/30, 8/10 to 8/18, 8/11 to 12/31.

Simple unique indexes/constraints do not prevent this. More complex function based check constraints can, but are expensive. Some other techniques also exist, but are again expensive. Storing only one date (start date) and then looking for the closest greater start date as the "day after the end date" is also expensive (and then you may need to guarantee such a record exists).

Note: for SQL Server 2005 and less, if you intend only midnight based dates, further check constraints are required to enforce a midnight time portion of the datetime/smalldatetime fields, otherwise all applications need to do work to zero out times (non-midnight times on "date" (small)datetime fields tend to accrue over time).
jcrawf02
jcrawf02
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1610 Visits: 19324
Not sure I completely follow what Nadrek is saying, but had a thought. (who knew?)

How in a global enterprise would you account for sales beginning/ending across time zones? Multiple records for each? Or adjust based on local time in the application?

---------------------------------------------------------
How best to post your question
How to post performance problems
Tally Table:What it is and how it replaces a loop

"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."
Richard Gardner-291039
Richard Gardner-291039
SSC Veteran
SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)

Group: General Forum Members
Points: 265 Visits: 421
Just do it in your business logic - when you want to add a product to a promotion ensure the application has to call a stored procedure - in that procedure first check to see whether the product is already in a promotion. If it is then check the dates don't overlap. If they do then throw an error.

I can't see any inherent advantage in building this into the database structure, unless you're so chaotic you're allowing developers to throw data into your database at random.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search