Table Design - Challenging Math Problem - Part II 1

[donutman]

In thread thread183-1094475, vongrunt outlined a Coupon Definition table to be used by a program running a cash register. The next hurdle involving the Coupon Definition table is actually its child table which contains the ProductIDs to which a coupon can be applied. For example a coupon might allow 20% off the purchase of any assortment of various sweet rolls. So the child table would consist of the CouponID and a ProductID - one row for each ProductID.
The problem is that a coupon can provide for a 20% discount on any purchase. I don't want to create a row in the child table for every item in the Product Table. What's the best way to keep the SQL simple when determining which items purchased by a customer apply to this coupon?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[TJRTech]

Have a boolean (bit) column for the Coupon Definition and name it "TotalPurchaseDiscount", or "AnyPurchaseDiscount", or somesuch. In this way, when this column is true you know that you need to honor and calc the deduction against the purchase subtotal, else if not true, only against those product quantities associated with the coupon as defined by records in an associative table.

I suspect the SQL for that would have an OR in your WHERE clause

TJR

 

[donutman]

OMG, how fast I lose my SQL skills when away from it! The OR does the trick in the ON clause of the left join of the Transaction Detail to the Coupon Child table. What's really sad is that I had to test it to be sure that it would work correctly when the Coupon Child table returns an empty set!
Thanks, Karl.

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[chrissie1]

Welcome back karl ;-)

Christiaan Baes
Belgium

If you want to get an answer read this FAQ faq796-2540
There's no such thing as a winnable war - Sting