在 SQL-Server 中设计表时需要建议 [英] Need advice in designing tables in SQL-Server

问题描述

我有一个包含项目的报价单(存储在表 QuoteItem 中):
QuoteItemId、QuoteId、ItemId、数量等

I have a quote that contains items (store in table QuoteItem):
QuoteItemId, QuoteId, ItemId, Quantity etc.

现在，我需要能够在报价单中创建一组选定的项目并对其应用折扣.嗯，这很简单，我再创建两个表:
组:GroupId、折扣百分比
GroupQuoteItem:GroupId、QuoteItemId

Now, I need to be able to create a group of chosen items in the quote and apply a discount on it. Well that's simple, I create two more tables:
Group: GroupId, DiscountPercentage
GroupQuoteItem: GroupId, QuoteItemId

假设我的报价中有 30 个项目.我创建了一个包含报价中 1-20 项的组，并对其应用了折扣.现在我需要另一个包含 10-30 件商品的组，问题是关于那些内部 10 件商品，我需要控制折扣是应该在其他折扣之后应用于商品还是应该在商品的基本价格上.
例如，我要谈谈项目号.15 在报价中: QuoteItem.Cost = 100我申请了 10% = 90 的第一次折扣.
现在我想申请第二个折扣，我需要能够控制折扣是应该在 100 上还是应该在 90 上.
当我有多个折扣组并且我想应用复杂的折扣架构时也是如此.

Let's say I have 30 items in a quote. I made a group that contains items 1-20 from the quote and I applied a discount on it. Now I need to have another group that contains items 10-30, the problem is about those inner 10 items, I need to control whether the discount should apply on the items after the other discount or it should be on the items' base price.
For instance, I am gonna talk about item no. 15 in the quote: QuoteItem.Cost = 100 I applied 1st discount of 10% = 90.
Now I want to apply the second discount, I need to be able to control if the discount should be on the 100 or should be on the 90.
Same is when I have multiple discount groups and when I wanna apply a complex architecture of discounts.

任何帮助将不胜感激.

推荐答案

我会考虑向 GroupQuoteItem 表 GroupQuoteItem.Priority 添加一列.此列将用于确定最终价格的查询中.如果您有 N 个具有相同、最高优先级的折扣，它们将相互堆叠(顺序无关紧要，这要归功于乘法的结合性).

I would look into adding a column to the GroupQuoteItem table, GroupQuoteItem.Priority. This column would be used in the query that determines the final price. If you have N discounts with the same, highest priority, they will be stacked atop each other (the order doesn't matter, thanks to associativity of multiplication).

如果所有这些高优先级的折扣后来都被删除，则低优先级的折扣可以取而代之.这应该可以帮助您设置非常复杂的折扣结构.

If all of these high-priority discounts are later removed, lower-priority discounts can take their place. This should help you in setting up pretty complex discount structures.

我希望至少能给你一个起点.

I hope that at least gives you somewhere to start from.

这篇关于在 SQL-Server 中设计表时需要建议的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！