Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
petehotchkiss
Starting Member
2 Posts |
 Posted - 2005-04-11 : 11:59:45
|
| I'm in the process of scoping out the next phase of a project where an existing e-commerce site will be updated to have special offers added.The core of the system has 3 main tables. A product_catalogue table, a orders table, and a product_look_up table to relate the primary keys in the orders table to items in product_catalogue.How best is it to add special offers into such a system ?For example, purchasing two particualr titles will give user a 25% reduction in total cost, or entering a special code will gain the user a free something-or-other (pen, t-shirt...)I've thought about possibly having a new table offer_rules that each order when entering the payment process is checked against, but this could get unweildly quickly.Any advice on how to progress greatly appreichaited.Pete |
|
|
jhermiz
3564 Posts |
Posted - 2005-04-11 : 14:34:03
|
But when the promotion ends cant you delete it from this table ?
Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url] |
 |
|
|
petehotchkiss
Starting Member
2 Posts |
Posted - 2005-04-12 : 07:41:55
|
| No - becuase we want to be able to look back over certain promotions, and see how effective they were. So if we delete it then the relationship between and order and an offer is broken and therefore useless. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-04-12 : 12:14:18
|
| We have a couple of tables that make up the Promotions feature on our Web Store product.The header table defines the start/end date of promtion, name, description etc. Plus the number of different "qualifying products" and "qualifing qty". Also the type: cheapest is reduced; additional free product to be added; all products to be at discounted price; etc.Then we have a JOINed child table of the products which form that promotion. For each product there is a qualifying qty. and a promotional price / discount %age.The Qualifying Products is the number of different products the shopper must buy to qualify.Thge Qualifying Qty is the number of items they must buy.So ... say I have 5 trees in my promotion and a Qualifing Qty of 3 - buy any 3 trees and you qualify. If the Qualifying Products was 3 then you would have to buy 3 different products.Once the qualifications have been met the discount applies. So I can set the price/discount %age on each product to be, say, 50% off - so my 3 trees are now half price.Or I can set the promotion type to be "cheapest is reduced" and then set the discount %age to 100% - so now the cheapest tree is free. Or I could make it 50% off. Or $1 ...So I can create straight discount: Buy any four of any combination of these products at a discount price.Or Buy this book, and also this other book, for a special price.Or Buy any five of these and get the cheapest free. Or get this additional product free.We then have a separate process that reduces everything by 10% - or all products in Product Group X. We do this by storing a Current Price and a Full Price. The Full Price is the original non-Sale price. If the Current Price is less then it shows on the web site as a strikeout with the "Sale Price" alongside.We also have bulk discount using qty breaks - so the price for 1-4 is $10, 5-9 = $9, 10+ = $8So far that has covered anything we've been asked for by clients!We also have a voucher system which allows separate carriage rates (usually "Free" or "Priority"), specific discount, and specific Promotions to become available - i.e. they are only available on presentation of a voucher that "activates" them.Kristen |
|
|
|
|
|
|
|
|
creating special offers in e-commerce