Mysql – Modeling discounts – Product specific and Customer specific discounts

database-designMySQLnormalization

I have a reasonably simple database but have the following problem:

I'm modeling a store + a lot of non-relevant stuff database. I have the following problem. I want to allow for specific products to be discounted, but also provide discounts to all purchases by a specific customer (I have a table that holds customers). Now when I think of it, I might even want for specific stores to have discounts or maybe discount specific products to specific customers or any combination of these.

Normally, the sane way to approach this would be to create a table with sales, and maybe another one with sale types. But I really can't wrap my head around it. How would it actually look like? Any help?

I think that my actual DB model isn't relevant, but I'll update the post with it if it's needed.

Best Answer

You should have a table for each class of thing you want to offer a discount on. From your question that would be a table each for products, customers and stores. Most likely you will have these tables in your design already. Each will have a column of an appropriate type to hold the discount. You don't say what you will offer but a percentage discount could be a TINYINT UNSIGNED since it can only be in the range 0 to 100 or a DECIMAL if you prefer to handle your math that way, and so forth if you'll offer fixed-amount discounts ("$10 off your purchase over $120. This month only!"). Name your column to match your rule e.g. DiscountPercent or DiscountAmount. If it is important to track changes to discounts over time separate tables keyed by date will be required e.g. CustomerDiscount (CustomerID, StartDate, EndDate, DiscountPercent).

Be sure to work out with your business how these rules interact. If I'm a 5% customer buying a 6% product do I get an 11% discount or a (0.95 * 0.94) price? For a $100 item will I be charged $89.00 or $89.30? Can I even get more than one discount on a single purchase? Which?

If only a few combinations receive the discount you could have an intersection table keyed by, say, product and customer as you mention in your question. This works up to a couple of levels then gets very unwieldy. Then a general decision table will be your best bet. This will have a column for each of your decision variables (product, customer and store in your examples) and another for precedence/ sort order. You manually assign the precedence to match your rules and at run time pick the highest precedence which matches this sale's parameters.

An example:

Store      Product  Customer  Precedence  Discount%
Sao Palo                               1          2
Melbourne                              1          2
           Foo                         2          3
           Bar                         2          3
                    Chris              3          5
                    Sam                3          5
           Foo      Sam                4          7

Sam buys some Foo in Melbourne. She gets 7% off since that's the row with the highest Precedence that matches all the criteria (or has a NULL). Anything else Sam buys has a 5% discount. Anyone else who buys Foo gets 3% off.

It is usual to copy relevant values to the Sales table for each transaction. This gives you
a) a complete audit history, which accountants like
b) flexibility to award non-standard discounts.

Related Question