db design questions (how to deal with groups of products, to make 2+2=3)

Question

I would be grateful if somebody could help me to find an elegant solution to this database design problem. There is a company with a lot of different products (P1,P2,P3,P4) and a lot of customers (C1, C2, C3, C4). Now they have a simple database table to deal with orders, something like 20101027 C2 P1 qty status 20101028 C1 P2 qty status

Now I would like to create groups of products ( eg. (P1+P3+p4) and (P2+P3)) that could be purchase together for a reduced price. What is the best way to represent such groups in a database system? Dealing with these groups as individual products doesn't work, because I need the functionality of replacing, adding or removing products from the groups. So I need to keep the currently given table of products.

Thanks for reading. I hope I will get some help.

Answer 1

Add a new table product_group_promotions, with an ID, name and discount price. Then create a table product_group_promotions_products that links products to product group promotions. This will contain a product group ID and a product ID. This way, you can place one product in multiple groups, and let groups contain multiple products (of course).

Answer 2

Jan's answer is correct but incomplete.

You'll also need start and end dates of the promotion. You'll probably want to enter next week promotions so they are ready but not apply them until appropriate.

Discount price may not be enough either. You also will need to get business rules from business people as to how to apply the discount. It could be a percentage or a free item or a fixed amount. If a percentage do you distribute the discount evenly, proportionally, on the cheapest product, the most expensive? If a free item, which one in the set is free. It could also be a fixed amount, $10 off if you buy x, y, and z. Is the discount applied more than once. If someone buys 5x of P2 and P3 do they get the discount on all of them or just the first ones. Is there a limit over a time period. As in the past example, if you don't give me the discount on all 5, I would just fill out 5 orders of 1 each and get the discount you were trying to prevent. If so you'd have to go back through previous purchase by that customer to see if they've received that discount.

You can see how ugly this can get. I would clarify with the business EXACTLY what they plan to use this new feature for and run through these use cases with them.

As Q, asked, if the basket of purchased items is large enough there could be more than one discount possible. Do you have to determine what to give, do you present a list of choices back to the UI... and the recalculate.

This is why I have mercy on department stores who screw this stuff up. It's not simple.

Answer 3

On the surface this is simple sounding but in reality it's very complex.

"Dealing with these groups as individual products doesn't work, because I need the functionality of replacing, adding or removing products from the groups."

Here are several things you need to look at:

1. Current Inventory vs Past Orders
2. How do you deal with Price Changes on P1, P2, P3
3. How do you handle adding a new product or products to an existing group
4. How do you handle removing a product or products from an existing group

In my opinion you need two sets of tables.

• Tables that make up your current inventory
• Tables that record what customers purchased (historical data tables)

If you need to reconstruct a customers purchase from six months ago, you can not rely on the current data givin the fact that a grouping may not look the same today as it did six months ago. So, if you do not already have a set of historical data tables for customer records then, I recommend you create them.

With a set of historical data tables that house what was bought by the customer you can pretty much do what every you want to the current invetory data. Change prices, regroup products, make products obsolete, Temporarily suspend a product, etc.