Table Design - Challenging Math Problem 2

[donutman]

Hi Guys, it's been a while I have an interesting problem. I need to create a Coupon Definition table that I can use to solve various possible discount scenarios for my new cash register software. What is the most efficient schema that can handle the following discounts:
1) Buy x get y free (eg. buy 1 get 1 at equal or lower value).
2) Buy x get next y at z% of full price.
3) Buy group of x and get y extra ones free (0% of full price) but limit of z (x+y<=z)
4) Buy groups of x at y% of full price but limit of z.
So the question is, "Is there a way to represent this as one rule?" My last resort is to just have a column that defines the type of coupon being presented, but it would be nicer to create just one very flexible rule. Some of those are clearly the same rule just worded differently, but there is a clear difference between eg. 3 and 4. The later provides a discount on the first x purchased whereas the former requires that the first x be purchased at full price. Does this make sense? Vongrunt...save me! BTW, feel free to add other % scenarios that I haven't thought of. Let's not work on the $ off scenarios for now...that will be next!
-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]

 

[Catadmin]

Karl,

Here are some off the cuff thoughts.

Table PurchaseDetails (pd for short)
PK Item# Qty Price CoupCode ...

Table CouponDetails (cd for short)
PK CoupCode Discount DiscType Max Min AmtLimit

Max & Min can be NULL. Min will allow you to set a minimum purchase qty (must buy 2 to get the third at half off). Type will be Percent or Dollar (or whatever) which makes it flexible for you later on.

Then, if someone gives you an Employee ID, you can choose CoupCode ED for Employee Discount (Which just happens to be 10% off all purchases totaled below $100.00).

So, do your join on CoupCode, check for Type, Min, Max and AmtLimit with a nested Case or If/Else statement, and do the math of @Total = (Price * Qty) / Discount.

Or, if the Price * Qty > $100, then @DiscTotal = $100 / Discount and @Total = @DiscTotal + ((Price * Qty) - 100).

Does this help?



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[vongrunt]

Obviously there is initial action (buy), optional reaction (get) and optional global limitation (but):

Buy ? x [at ? of full price]
[Get ? y [at ? of full price]]
[but limit of ?]

?'s become column candidates in coupon definition table. At least one of [optional] parameters must be present; otherwise you get pass-thru rule that doesn't do anything.

Personally I'd probably introduce "at least/at most" operators on some parts. Do you have Media Player handy (hint: auto-playlists)?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[donutman]

Very nice guys! Catadmin, thanks for the idea of having a "standard" couponID for employee purchases. That's a good way to create some frequently used discount schedules.
Vongrunt, well done...just as I suspected. You have a knack for boiling down the words to their essence. Your presentation made it easy to add the $ off scenarios with the inclusion of another "get $". Naturally, it would not be realistic to have both gets operable on the same coupon.
Catadmin, you made me realize that there is another type of coupon (employee purchase coupon is an example) where there is a $ limit rather than item count to the purchase. Another words some coupons are good for 20% off with a limit of $100 purchased (or max $20 off). To solve the problem, I guess I need a 2nd [but limit of $].
I'll present the next hurdle in another thread.
-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]