数据库架构 - 可配置字段？

Question

数据库架构 - 可配置字段？

mysqldatabasedatabase-designrelational-database

4

我出售潜在客户信息，并按以下方式向客户收费：（只能从以下付款类型中向客户收取一种付款方式）

按潜在客户付费：每月前__个潜在客户收取$__
每月接下来的__个潜在客户收取$__
每月接下来的__个潜在客户收取$__
以此类推...

按约会付费：每月前__个潜在客户收取$__
每月接下来的__个潜在客户收取$__
每月接下来的__个潜在客户收取$ __
以此类推...

按销售额百分比付费：每笔销售的价格的__%（每笔销售）

我的问题是：在这种情况下，最好的数据库设计解决方案是什么？

我尝试过的方法：

+---------+ 
| clients |
+---------+
| id      |
| name    |
+---------+

+---------------+
|     deals     |
+---------------+
| client_id     |
| max_quantity  | 
| cost          |
| unit_type     |
+---------------+

因此，客户 id 为1的记录可能如下所示：

+-----------+--------------+---------------+-------------+
| client_id | max_quantity | cost_per_unit |  unit_type  |
+-----------+--------------+---------------+-------------+
|         1 |           10 |            10 | lead        |
|         1 |           30 |             5 | lead        |
|         1 |          100 |             2 | lead        |
|         1 |           10 |            35 | appointment |
|         1 |           30 |            20 | appointment |
|         1 |          100 |            10 | appointment |
|         1 |         1000 |             5 | appointment |
|         1 |            0 |            50 | sale        |
+-----------+--------------+---------------+-------------+

现在上面的表格意味着：
每个潜在客户将收取$10，最多10个潜在客户
每个潜在客户将收取$5，最多30个潜在客户
每个潜在客户将收取$2，最多100个潜在客户
每个预约将收取$35，最多10个潜在客户
每个预约将收取$20，最多30个潜在客户
每个预约将收取$10，最多100个潜在客户
每个预约将收取$5，最多1000个潜在客户
每个销售将收取$50
此外，我想添加x条类似规则（每个潜在客户，每个预约，每个销售）
我个人认为我的方法不是最好的解决方案。期待聪明人的回复！谢谢。
附：我知道unit_type可以进一步规范化，但这不是问题 :)
更新：
也许我可以存储序列化数据？

- Lucky Soni

“$__ for the first/next __ leads per month” 是指“本月销售给客户的第一个/下一个__潜在客户的价格为$__”吗？ - dened

@dened 不是每月的费用，而是根据数量差异定价。例如，前10个潜在客户每个潜在客户收费$20，接下来的20个潜在客户每个潜在客户收费$10，以此类推... - Lucky Soni

是的，我看到这是基于数量的差异定价。我问你这里的数量是什么？它是当前月份销售给客户的前/下一个y潜在客户的x/潜在客户吗？ - dened

@dened 哦，我现在明白了。是的。 - Lucky Soni

我正在努力理解你的付款方案，但对我来说仍然不太清楚...利用你的例子，如果客户一次购买50个线索并有2个约会，你会收取多少费用？如果这个客户在同一个月再购买100个线索并再安排1个约会，这次你会收取多少费用？请描述你的计算过程。 - dened

@dened 对不起，每个客户只有一种付款方式。我已经更新了我的问题以反映这一点。 - Lucky Soni

3个回答

2

我假设更改操作很少发生（更新/插入），大部分情况下您使用选择语句来计算成本，因此我提出这种设计，使用选择语句计算成本非常简单。

+-----------+--------------+---------------+---------------+--------------+------------+
| client_id | max_quantity | min_quantity  | cost_per_unit | default_cost | unit_type  |
+-----------+--------------+---------------+---------------+--------------+------------+
|         1 |           10 |            0  |           10  |            0 |        lead|
|         1 |           40 |           10  |            5  |          100 |        lead|
|         1 |          140 |           40  |            2  |          250 |        lead|
|         1 |           10 |            0  |           35  |            0 | appointment|
|         1 |           40 |           10  |           20  |          350 | appointment|
|         1 |          140 |           40  |           10  |          950 | appointment|
|         1 |         1140 |          140  |            5  |         1950 | appointment|
|         1 |            0 |            0  |           50  |            0 |        sale|
+-----------+--------------+---------------+---------------+--------------+------------+

选择查询的样子如下：

select 
   default_cost + ($quantity - min_quantity) * cost_per_unit
from 
   table
where 
   unit_type = $unit_type 
   and (max_quantity >= $quantity or max_quantity = 0)
   and $quantity >= min_quantity

- Hieu Vo

1

如果您考虑到成本计算业务逻辑可能会在未来发生变化，并且您不需要根据计算常量筛选/排序表格，我建议为rule_id设置一列，这基本上就像您的unit_type，以及一个名为properties的varchar列，其中存储了该规则所需的所有特定值，并带有分隔符。

然后，您可以检索适用于您的客户的规则并在您的业务逻辑中进行计算。如果您需要一个突然需要5个参数的新规则，则不需要更改数据库模式。只需在您的业务逻辑中编写新的rule_id代码即可。

当然，如果您喜欢将计算逻辑移入存储过程和/或需要按规则属性进行过滤或排序，则应该为每个规则参数单独设置列...

- Mattias Åslund

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Steve Chambers · Accepted Answer

你提出的架构是一个不错的起点，有一些优点。在我看来，不太优雅的部分是重复使用unit_type值和sale中非功能性的max_quantity值。

建议将deals拆分为三个表，而不是一个表。个人认为应该使用单数而不是复数的表名，并以相同的前缀开头，这样它们就可以靠近彼此列出：类似于commission_lead、commission_appointment和commission_sale。

** [关于这个问题有很多争议 here]

还建议在每行中包含下限和上限。这确实会使用比严格需要更多的数据，但认为这样做是值得的，因为它应该使表格数据更易读，并简化计算查询。

因此，建议的新架构如下：

+---------+ 
| client  |
+---------+
| id      |
| name    |
+---------+

+-----------------+
| commission_lead |
+-----------------+
| client_id       |
| min_quantity    |
| max_quantity    | 
| cost_per_unit   |
+-----------------+

+------------------------+
| commission_appointment |
+------------------------+
| client_id              |
| min_quantity           |
| max_quantity           |
| cost_per_unit          |
+------------------------+

+-----------------+
| commission_sale |
+-----------------+
| client_id       |
| cost_per_unit   |
+-----------------+

而且 client_id = 1 的记录如下：

commission_lead
+-----------+--------------+--------------+---------------+
| client_id | min_quantity | max_quantity | cost_per_unit |
+-----------+--------------+--------------+---------------+
|         1 |            0 |           10 |            10 |
|         1 |           11 |           30 |             5 |
|         1 |           31 |          100 |             2 |
+-----------+--------------+--------------+---------------+

commission_appointment
+-----------+--------------+--------------+---------------+
| client_id | min_quantity | max_quantity | cost_per_unit |
+-----------+--------------+--------------+---------------+
|         1 |            0 |           10 |            35 |
|         1 |           11 |           30 |            20 |
|         1 |           31 |          100 |            10 |
|         1 |          101 |         1000 |             5 |
+-----------+--------------+--------------+---------------+

commission_sale
+-----------+---------------+
| client_id | cost_per_unit |
+-----------+---------------+
|         1 |            50 |
+-----------+---------------+