数据库设计：如何对产品/服务的通用价格因素进行建模？

Question

我正在尝试创建一个通用数据模型，该模型允许特定产品（由下面示例表中的 FK Product_id 表示）指定 0 个或多个价格“因子”（我将“因子”定义为添加的价格单位）或减去以获得总数）。

假设有这样的表格：

===============================
price
===============================
price_id (PK)
product_id (FK)
label
operation (ENUM: add, subtract)
type (ENUM: amount, percentage)
value

一本书的价格可以这样表示：

====================================================================
price_id | product_id | label      |  operation | type       | value
====================================================================
 1       | 10         | Price      | add        | amount     | 20  
 2       | 10         | Discount   | subtract   | percentage | .25  
 3       | 10         | Sales Tax  | add        | percentage | .1

这基本上意味着：

Price:      $20.00
Discount:  - $5.00 (25%)
--------------------
Sub Total:  $15.00
Sales Tax:   $1.50 (10%)
------------------------
Total:      $16.50

几个问题：

1. 初始设计有什么明显的错误吗？
2. 如果我想创建“模板”（例如，具有“价格”、“折扣”和“销售税”字段的“日用商品”模板；具有“价格”、“折扣”、“奢侈品税”字段的“奢侈品”模板，该怎么办？ “字段）-我将如何建模？
3. 如果每条记录都适用于前一条记录的总数，则上述模型有效。因此，在示例中，“销售税”适用于“价格”和“折扣”的差额。如果总计不是这么简单计算的怎么办？例如：A + B + (A + 10%) - (B - 5%)。我将如何建模？
4. 另外，如果“百分比”类型不适用于紧邻的前一行（如问题 #3 所暗示的）并且适用于多于 1 行，该怎么办？我是否需要另一个表来逐项列出百分比适用的价格->price_id？

Answer 1

首先，您需要一个价格标签模型，这很简单：

price_labels
 id | label 
  1 | Price 
  2 | Discount 
  3 | Tax

然后是您给出的示例表的稍微修改版本：

products_prices
price_id|product_id|label_id|divider|value 
  1         10        1        1      20 
  2         10        2        100   -25 
  3         10        3        100    10 

这里我只是用 id 中的相应 id 替换标签。 i>price_labels 表作为外键。此外，我省略了 type 字段，该字段很简单，因为值可以是正浮点数或负浮点数。我添加了分隔列以启用百分比参数。我认为这种方式也更容易阅读，因为你说（并认为）“负百分之二十五”而不是 0.25 。

现在表达“抽象”部分有点复杂，可能有很多解决方案。

price_expressions
product_id | date_from          | date_until          | expression
  10       |2011-11-02 04:00:00 |2011-11-12 04:00:00  | (SELECT divider*value from 
                                                         products_prices 
                                                         WHERE product_id=%PRODUCT_ID%    
                                                         AND label_id=1)*
                                                        (SELECT 1+value/divider from products_prices 
                                                         where product_id=%PRODUCT_ID% AND 
                                                         label_id=2)*
                                                        (SELECT 1+value/divider from products_prices 
                                                         where product_id=%PRODUCT_ID% AND                                  
                                                         label_id=3)

在表达式字段中，您可以存储复杂的 SQL 语句，在其中您只需将 %PRODUCT_ID% 占位符替换为同一行中的 Product_id 值：

SELECT REPLACE(expression,'%PRODUCT_ID%',CAST(product_id AS char)) 
AS price_expression FROM price_expressions 
WHERE product_id = 10 AND date_from>=DATE_OF_PURCHASE 
AND date_until<=DATE_OF_PURCHASE

在我看来，这有两种可能的变体：

1. 您可以更改 Product_id= %PRODUCT_ID% 和 label_id=N 条件仅包含price_id=N，因为您已将其存储在 products_prices 表中
2. 您可以使用其他表达式格式，例如 %PRICE_ID_1%*%PRICE_ID_2 并执行替换和计算应用程序级别不直接在 SQL

希望这有帮助。

Answer 2

这似乎有点过度设计。

1）销售税百分比难道不是购买该商品的地点而不是购买哪种商品的一个因素吗？我可以看到“IsTaxable”字段，但指定每个项目的税率似乎不正确。

2) 您确定需要承担制造该仿制药的费用吗？您是否已经相当确定未来还会有更多因素？如果没有，请不要使其过于复杂化。

建议设计：
- 向产品表添加 IsTaxable、DiscountPct 和单价列。
- 将销售税百分比存储在另一个表中。大概是发票表。

Answer 3

关于您的问题 1：

标签、操作和类型之间存在潜在的功能依赖性。例如，折扣可能总是意味着减法和百分比。如果是这样，可以通过将这些字段移动到一个单独的表中，并将 label 作为 PK 来标准化数据模型。

顺便说一句，非规范化数据模型可能是提高性能和/或简单性的合法工具。

关于您的问题2：

这是一个可以轻松“模板化”的模型：

产品的最终价格是通过对 PRICE 应用一系列步骤来计算的，按照 STEP_NO 定义的顺序。多个产品可以轻松共享相同的“模板”（即相同的PRICE_ADJUSTMENT_ID）。

关于您的问题 3 和 4：

您需要对完整的表达式树进行建模，而不仅仅是一系列“线性”步骤。有多种方法可以做到这一点，其中大多数在关系范式中相当复杂。也许最简单的方法是保持数据模型与上面类似，但将其视为逆波兰表示法。

例如...

A + B + (A + 10%) - (B - 5%)

...可以表示为：

OPERATION    TYPE       VALUE
----         ----       -----
             value      A
             value      B
add
             value      A
             percentage 10
add
add
             value      B
             percentage 5
subtract
subtract

您确定您确实需要这种功能吗？

Answer 4

如果某些价格因素取决于商品的类型，则您将拥有一组链接到 ItemType 表中的实体的价格因素，并且 ItemType 将是商品实体的属性（引用 ItemType 的外键）。如果其他价格因素与销售或运输商品的区域设置相关联（例如销售税），则这些因素将与区域设置相关联，并根据客户的地址进行调用。您通常会在行项目级别应用项目类型因素，并将区域设置驱动的因素应用到发票总额。 Sin-tax 将链接到 ItemTypeLocale 二元组，并应用于行项目级别。

Answer 5

1/我认为你还需要考虑顺序

，例如价格 - 折扣 + 销售税显然是可以接受的，但价格 + 销售税 - 折扣则不然，价格 - （折扣 + 销售税）

2/ 我会考虑在另一张表中列出价格。这不是所售商品的详细信息吗？例如，蓝色小部件，20.00 美元。而您的因素是销售类型的详细信息。据推测，您可以为上门零售销售设置一组因素，为在线销售设置另一组因素，为批发销售设置第三组因素。您可以根据基本价格 * 系数计算这三种销售类型的实际价格。

3/我认为你需要更多的桌子；例如，可能是“商品”、“销售类型”以及“factor_details”和“factor_rules”。您的销售类型可能包含在您的奢侈品示例中，在这种情况下（如果某件商品只有一种销售类型），这可能会出现在商品表中。 Factor_rules 详细说明计算公式，factor_details 详细说明值。

我觉得这很有趣。一旦您解决了这个问题，我将不胜感激地用您的经验更新这个问题。