I have a typical bespoke products / shopping cart system that I've been asked to upgrade to enable promotions of various types. existing system tbl_Products ProductId (PK) Name Price etc tbl_Cart ItemID (PK) VisitorId (FK) ProductId (FK) Quantity Pretty obvious and basic. Are there any good worked examples of promotional systems. e.g. buy X and get 50% off Y. 3 for the price of 2 etc I'm really looking at the guts of how this works and the best way forward in terms of the database and system design. What is a sensible database structure. How best to calculate these things on the fly. etc
"Buy X and get Y off) has almost nothing to do with the database. You could have a "promotional" table, listing what you have to buy, how much you get off and the dates that promotion is effective for, but the actual business of changing the price in the cart is code behind the page, not database.
It will need to do the heavy lifting in the database (Stored Procs) For example say I have 10 products in a basket and this gives you eligibility for savings accross a range of products. e.g. buy a TV over £500 and you get 10% off a range of accessories , DVD players and DVD's. Buy a DVD player and save a firther 10% on DVD's etc the database will need to be returning pretty much the complete basket and automatically be adding in any free items etc. I don't want to be pulling thousands of records to do the analysis on the Web servers. As such the promotions data needs to be strucutred effectively to ensure the database can return the appropriate details. The web servers should really only be responsible for UI and validation.