October 1, 2009 at 7:51 am
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
October 1, 2009 at 7:57 am
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"
October 1, 2009 at 8:24 am
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).
October 1, 2009 at 8:29 am
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[/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."
October 1, 2009 at 8:31 am
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.
October 1, 2009 at 8:39 am
jcrawf02 (10/1/2009)
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?
Been there, not going there again. The short answer is VERY TEDIOUSLY.
October 1, 2009 at 9:05 am
Nice article - looking forward to see how it plays out.
October 1, 2009 at 9:41 am
Nadrek (10/1/2009)
...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....
Business rules might allow simultaneous promotions. I've rented cars online before and had to enter a promotion code for discounted rates (eg. Senior Citizen or Emerald Aisle). The person/process entering the order has to be aware of the promotion, of course. The "promotion code" (promotion PK) can be placed in the OrderHeader or OrderDetail table depending on whether or not business rules allow multiple promotions in a single order. Also, if regular prices are included as "promotions", a left join (and associated overhead) is not required to retrieve product pricing and historical reporting (eg. reprinting an order form) does not require date comparisons. This also treats non-sale price changes in a temporal fashion and supports pre-entry of price change information.
October 1, 2009 at 9:43 am
awww! I thought this article was on soft skills... badly needed in the DBA community. 😀
thanks
SQL_EXPAT
October 1, 2009 at 9:47 am
Richard Gardner-291039 (10/1/2009)
You're talking about a different data set, that information is in your sales ledger.
You have a point, however, if I'm trying later on to provide data to marketing with regards to discounts and their effects on sales, the resulting price per unit on the ledger may not provide me with a reliable enough number. Having the ability to get the exact PPU during that promotion would be, in my opinion, better served by having a way to recreate the exact price at that time without having to go to the ledger, the end price of which may be affected by other factors such as good customer discounts, volume discounts, etc (as you mentioned). Most of that discussion is admittedly academic without discussing the actual schema and business rules for that particular "what if". 🙂
Richard Gardner-291039 (10/1/2009)
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"
All excellent points. Again, I think the best approach on this one would likely be situational.
October 1, 2009 at 10:14 am
I use Ceridian HR/Payroll which uses this scheme. While I agree fully with the concept, there is a huge performance hit.
Have you ever seen the Execution plan when you try to run any query that uses Temporal Dating? In a nutshell, plan on nested full-table scans, especially in the case you have multiple related tables with effective dating. You're putting "OR" clauses on not single fields, but two fields based on a range of dates.
Plus, as was mentioned before, there's no primative, database-level way to prevent overlap.
This is where a datawarehouse, in a flat-schema (not star) model comes in handy. In payroll, each day we have a transaction--person A worked X hours. His pay scale on that day was X. Once that day is done, that 'X' payscale is set in stone--it can be loaded to a transaction-detail table with the payscale embedded in the table--not joined to the 'payscale' table with effective dating, and all reporting is ultra fast and accurate. This is true for the numerous key fields Ceridian uses that are joined from a dozen or more 'effective dated' tables.
Just my thoughts...
--Jim
October 1, 2009 at 10:35 am
GSquared (10/1/2009)
I have to mention that seeing the title of this article made me wonder if SSC had changed into a different type of website overnight. 😉
Heh, so did I. I have to admit though that like many developers, I could maybe use some pointers there as well...
Dan Guzman - Not the MVP (7/22/2010)
All questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time.'
October 1, 2009 at 10:39 am
Yes...I'm glad I'm not the only one who thought this was a "How to pick-up chicks" article!
October 1, 2009 at 11:01 am
Wow 'em with your disaster recovery model! 😛
---------------------------------------------------------
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."
October 1, 2009 at 11:46 am
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:
Viewing 15 posts - 16 through 30 (of 46 total)
You must be logged in to reply to this topic. Login to reply