Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Effective Dating Series Part I - The Problem Expand / Collapse
Author
Message
Posted Thursday, October 1, 2009 6:57 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:54 PM
Points: 41, Visits: 370
You said "heinous"
Post #796289
Posted Thursday, October 1, 2009 7:04 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 25, 2014 6:31 AM
Points: 2,656, Visits: 19,187
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."
Post #796293
Posted Thursday, October 1, 2009 7:14 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, April 17, 2014 3:29 AM
Points: 238, 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.
Post #796298
Posted Thursday, October 1, 2009 7:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 27, 2014 1:14 PM
Points: 2, Visits: 30
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.
Post #796319
Posted Thursday, October 1, 2009 7:41 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 25, 2014 6:31 AM
Points: 2,656, Visits: 19,187
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."
Post #796322
Posted Thursday, October 1, 2009 7:51 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #796336
Posted Thursday, October 1, 2009 7:57 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, April 17, 2014 3:29 AM
Points: 238, 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"
Post #796343
Posted Thursday, October 1, 2009 8:24 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, July 24, 2014 8:41 AM
Points: 861, Visits: 2,357
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).
Post #796366
Posted Thursday, October 1, 2009 8:29 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 25, 2014 6:31 AM
Points: 2,656, Visits: 19,187
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."
Post #796370
Posted Thursday, October 1, 2009 8:31 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, April 17, 2014 3:29 AM
Points: 238, 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.
Post #796371
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse