数据库设计问题（如何处理产品组）
如果有人能帮助我找到一个优雅的解决方案来解决这个数据库设计问题，我将不胜感激。有一家公司拥有很多不同的产品（P1，P2，P3，P4）和很多客户（C1，C2，C3，C4）。现在他们有一个简单的数据库表来处理订单，比如 20101027 C2 P1 数量状态 20101028 C1 P2 数量状态
现在我想创建可以以折扣价一起购买的产品组（例如（P1+P3+p4）和（P2+P3））。在数据库系统中表示此类组的最佳方式是什么？将这些组作为单独的产品进行处理是行不通的，因为我需要从组中替换、添加或删除产品的功能。所以我需要保留当前给出的产品表。
感谢您的阅读。我希望我能得到一些帮助。
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
添加一个新表
product_group_promotions，其中包含 ID、名称和折扣价格。然后创建一个表product_group_promotions_products，将产品链接到产品组促销活动。这将包含产品组 ID 和产品 ID。这样，您可以将一个产品放入多个组中，并让组包含多个产品（当然）。Add a new table
product_group_promotions, with an ID, name and discount price. Then create a tableproduct_group_promotions_productsthat 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).简的回答是正确的但不完整。
您还需要促销的开始日期和结束日期。您可能希望参加下周的促销活动，以便准备好，但在适当的时候才应用它们。
折扣价可能也不够。您还需要从业务人员那里获取有关如何应用折扣的业务规则。它可以是百分比、免费项目或固定金额。如果按照百分比将折扣平均分配给最便宜的产品和最贵的产品？如果是免费物品，则该套装中的哪一件是免费的。也可以是固定金额，如果您购买 x、y 和 z，可享受 10 美元折扣。折扣是否多次应用。如果有人购买 5 倍的 P2 和 P3，他们会获得全部折扣还是仅获得第一批折扣。在一段时间内是否有限制。与过去的示例一样，如果您不给我所有 5 个订单的折扣，我只会填写 5 个订单，每个订单 1 个，然后获得您试图阻止的折扣。如果是这样，您必须返回该客户之前的购买，看看他们是否收到了该折扣。
你可以看到这会变得多么丑陋。我将向企业明确说明他们计划使用此新功能的目的，并与他们一起运行这些用例。
正如 Q 所问，如果购买的商品篮子足够大，则可能有不止一个折扣。您是否必须确定要提供什么，是否将选项列表返回给 UI...然后重新计算。
这就是为什么我对那些把事情搞砸的百货商店表示怜悯。这并不简单。
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.
表面上这听起来很简单，但实际上非常复杂。
以下是您需要查看的几项内容：
我认为您需要两套表。
如果您需要重建六个月前的客户购买情况，则不能依赖当前数据，因为分组可能看起来不一样今天和六个月前一样。因此，如果您还没有一组用于客户记录的历史数据表，我建议您创建它们。
有了一组包含客户购买商品的历史数据表，您几乎可以对当前库存数据做任何您想做的事情。更改价格、重新组合产品、使产品过时、暂时暂停产品等。
On the surface this is simple sounding but in reality it's very complex.
Here are several things you need to look at:
In my opinion you need two sets of 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.