Hello.

I have a simple shop setup with a few tables; Customer, Category, Product, Order and OrderItems. Nothing special here.

Now, during some seasons/events (X-mas, Easter etc) it's nice to offer customers a discount on all or certain items in the product line.

Offering discounts for returning customers is also appreciated, bargain discounts (buy 2, get +1 for 40%), volume discounts (20% on 100+ items) or simple discounts like "get 20% on your first order".


First I thought I'll add a Discount table and simply connect the concerned products to it, but in the later scenarios it's certainly not enough.

I'm starting to think I'm way over my head here, unless someone could give me a nudge in the right direction.

Thank you.

Discounts can be tricky. Here is my solution to the question but it isn't all inclusive. Usually when you have buy 2 get 1 free it includes the phrase "of equal or lesser value". I haven't dealt with that. There are also lots of other issues you might run into, but at least here is one answer:

Discount

DiscountID
DiscountName
StartDate
EndDate
CustomerSpec - All, Returning, New
ProductSpec - All, Category specific, Product specific

DiscountVolume

DiscountID
StartCount
EndCount
DiscountPercent

DiscountCategory

DiscountID
CategoryID

DiscountProduct

DiscountID
ProductID

EXAMPLES:

Offering discounts for returning customers:

Discount: 1, 'Returning Customer 5%', 01/01/1900, 12/31/2099, 'R', 'A'
DiscountVolume: 1, 1, 999999, 5%

Bargain discounts (buy 2, get +1 for 40%) for specific category:

Discount: 2, 'Office Supplies - Buy 2, get 1 40% off', 10/1/2009, 10/31/2009, 'A', 'C'
DiscountCategory: 2, 77
DiscountVolume:

2, 1, 2, 0%
2, 3, 3, 40%
2, 4, 999, 0%

Volume discounts (20% on 100+ items) for specific product:

Discount: 3, 'Soccer Balls - 20% off after 100', 11/1/2009, 11/30/2009, 'A', 'P'
DiscountProduct: 3, 44
DiscountVolume:

3, 1, 100, 0%
3, 101, 999999, 40%

Simple discounts like "get 20% on your first order":

Discount: 4, '20% off your first order', 12/1/2009, 12/31/2009, 'N', 'A'
DiscountVolume: 4, 1, 999999, 20%

Thank you very much.
While you say it's not all inclusive I think it is quite a nice solution, myself I would never come near to anything like it.

Oh, and I apologize for not answering earlier.

It is good to hear my answer was helpful.