SQL Clone
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
Sam Bendayan
Sam Bendayan
SSC-Enthusiastic
SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)

Group: General Forum Members
Points: 195 Visits: 128
Comments posted to this topic are about the item Effective Dating Series Part I - The Problem
maurosalvai
maurosalvai
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 25
Hi.
I read the very interesting Article. But i have a little question... Why don't we implement the 2 dates into the promotion table instep off the promoted products? In this way we could not replicate the same dates for every product...

It could be a good idea or not? And if not, could you explain me the reason?

Thank you very much

Bye
Mauro
Stephen Byrne
Stephen Byrne
SSC Journeyman
SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)

Group: General Forum Members
Points: 92 Visits: 92
I'm inclined to agree with Mauro - to me it makes more sense to have the Promotion start and end date in the Promotions table, not the PromotedProducts table.

That then leaves the PP table design in a simpler state when you're asked to implement multiple pricings on the same product (i.e Buy 2 get 1 free OR Buy 6 get 4 free. Marketing departments do come up with this insanity!) which DO belong in the PP table.

This would also make your reporting easier, I think.

Well written article though, very easy to read and 100% clear in its delivery.
Richard Gardner-291039
Richard Gardner-291039
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: 1593 Visits: 421
Why not just have a mathematical price modifier attached to the promotions table rather than a promotion price - that way you can apply a promotion to a group of products without having to set up a load of new prices, this makes applying and unapplying promotions a hell of a lot easier regardless of whether you're using an expiry date or not.
robertm
robertm
Mr or Mrs. 500
Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)

Group: General Forum Members
Points: 538 Visits: 118
Arguably you could have the start/end dates at both levels! It really depends on the strategy you organisation takes. You might decide to have a particular promotion running for a finite period and then subsequently change the products that are included in that promotion. As you said Stephen, marketing departments come up with the most outlandish strategies some times.

I've been using temporal database design for a few years no and there's no question that it makes life easier down the track. I picked it up from a book 'The Data Model Resource Book' by Len Silverston which is well worth a read for any budding data modelers.



Stephen Byrne
Stephen Byrne
SSC Journeyman
SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)

Group: General Forum Members
Points: 92 Visits: 92
For sure, Temporal design can save many many headaches down the line.

A system I recently worked on had a very nice facility in addition to temporal data attached to the promotions and that was the ability to define how the pricing was done during the promotion- either a simple price change, a list of volume discounts, or a mathematical function.
SuperDBA-207096
SuperDBA-207096
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4345 Visits: 711
Good Article- seems like a pretty simple concept, have used it many times in the past.
GSquared
GSquared
SSC Guru
SSC Guru (119K reputation)SSC Guru (119K reputation)SSC Guru (119K reputation)SSC Guru (119K reputation)SSC Guru (119K reputation)SSC Guru (119K reputation)SSC Guru (119K reputation)SSC Guru (119K reputation)

Group: General Forum Members
Points: 119075 Visits: 9730
When I've done this before, I've kept the dates in the promotions table. I called it a "PricingScheme" table, since it included other things that just special promotions, but it's the same concept.

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. ;-)

- 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
ddunn-1115368
ddunn-1115368
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 59
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.
Garry Morris
Garry Morris
SSC Journeyman
SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)

Group: General Forum Members
Points: 84 Visits: 82
Agreed with RobertM, there is a place for dates in both tables depending on the business strategy. Imagine if you had the same promotion with a rolling list of products to be discounted (e.g. the "Two for One" promotion that lasts all summer long, but which features a new/different product every week).

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.
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