database-design - 订阅计费的数据库设计

Question

寻找有关定期计费系统数据库基本设计的一些指导。

我想出的设计有一个表来存储订阅的下一个周期(相同或新的计划，相同或不同的价格，或者不续订)，另一个存储应用的订阅(什么产品是什么时候买的，什么价格)。这就是我所拥有的:

Subscriptions
+----+------------+--------+-----------------+------------------+-------------------+
| ID | customerID | itemID | nextBillingDate | nextBillingPrice | notRenewingReason |
+----+------------+--------+-----------------+------------------+-------------------+
|  1 |         10 |      2 | NULL            |              280 | Too expensive     |
|  2 |         10 |      3 | NULL            |              120 | Too expensive     |
|  3 |         11 |      2 | 2015-06-18      |              290 |                   |
|  4 |         10 |      2 | 2016-10-14      |              290 |                   |
+----+------------+--------+-----------------+------------------+-------------------+


SubscriptionHistory

+----+--------+------------+------------+-------+--------------+-------+
| ID | subsID | startDate  |  endDate   | price | extInvoiceID | paid  |
+----+--------+------------+------------+-------+--------------+-------+
|  1 |      1 | 2012-09-04 | 2013-09-03 |   280 | 81654        | TRUE  |
|  2 |      2 | 2013-03-01 | 2013-03-31 |     0 | NULL         | TRUE  |
|  3 |      2 | 2013-04-01 | 2013-09-03 |   120 | 81812        | TRUE  |
|  4 |      1 | 2013-09-04 | 2014-09-03 |   280 | 84221        | TRUE  |
|  5 |      2 | 2013-09-04 | 2014-09-03 |   120 | 84221        | TRUE  |
|  6 |      3 | 2014-06-18 | 2015-06-17 |   290 | 85312        | TRUE  |
|  7 |      4 | 2015-10-14 | 2016-10-13 |   290 | 87421        | FALSE |
+----+--------+------------+------------+-------+--------------+-------+

它必须支持以下用例:

订阅期限为一年或三年

客户订阅产品计划

一个客户可以订阅多个产品

产品的附加组件可以包含在订阅中

可以通过订阅

部分添加附加组件

可以在订阅期间添加插件作为试用期

部分订阅可能会 Markdown (如因特殊情况同意免费二次订阅)

续订计划时，附加组件和价格可能会发生变化

能够记录不续订的原因

任何客户都应该可以看到完整的历史记录，例如在上面的数据库中，您可以看到客户 10:

加入2012-09-04

试用一个月后，于 2013 年 4 月 1 日在订阅中添加了一个附加组件

没有因为太贵而续订，所以于 2014-09-03

过期

2015-10-14 再次以更高的价格订阅，未付款

任何指针？

Answer 1

这是一个包含您的插件的表格。你没有明确说你的插件要花钱，但你提到了这一点，所以我已经包括了一个价格。我还假设插件与特定产品相关联。如果你的插件随着时间的推移而改变，我会有一个 beg_date和 end_date在此表中就像在 product 中一样 table 。

addon
    id              unsigned int(P)
    product_id      unsigned int(F product_id)
    description     varchar(255)
    price           double

+----+------------+-----------------+-------+
| id | product_id | description     | price |
+----+------------+-----------------+-------+
|  1 |          1 | This is addon 1 | 11.25 |
|  2 |          1 | This is addon 2 | 22.50 |
|  3 |          1 | This is addon 3 | 15.00 |
| .. | .......... | ............... | ..... |
+----+------------+-----------------+-------+

只是一个普通的老 customer table ...

customer
    id              unsigned int(P)
    salutation      varchar(4)
    first_name      varchar(50)
    ...

+----+------------+------------+-----+
| id | salutation | first_name | ... |
+----+------------+------------+-----+
|  1 |        Mr. |       John | ... |
|  2 |       Mrs. |       Jane | ... |
| .. | .......... | .......... | ... |
+----+------------+------------+-----+

这里是所有 addon的每一位客户都曾购买或试用过。在此示例中 end_date默认为 NULL并且在客户停止使用插件之前不会有任何值(value)。或者，您可以填写 end_date基于何时关联 product设置为过期。请注意，客户为插件 1 支付了全价，而没有为插件 2 支付任何费用(因为他们刚刚试用过)，并且他们以折扣价获得了插件 3。

customer_addon
    id                      unsigned int(P)
    customer_id             unsigned int(F customer.id)
    addon_id                unsigned int(F addon.id)
    beg_date                date
    end_date                date // default NULL
    price                   double
    renewed                 enum('f','t')
    decline_reason_id       unsigned int(F decline_reason.id)

+----+-------------+----------+------------+------------+-------+---------+-------------------+
| id | customer_id | addon_id | beg_date   | end_date   | price | renewed | decline_reason_id |
+----+-------------+----------+------------+------------+-------+---------+-------------------+
|  1 |           1 |        1 | 2015-01-10 | 2016-01-10 | 11.25 |       f |                 1 |
|  2 |           1 |        2 | 2015-01-10 | 2015-02-10 |  0.00 |       f |                 2 |
|  3 |           1 |        3 | 2015-10-25 |       NULL | 10.00 |    NULL |              NULL |
| .. | ........... | ........ | .......... | .......... | ..... | ....... | ................. |
+----+-------------+----------+------------+------------+-------+---------+-------------------+

这里是所有 product的每一位客户都购买过。在此示例中，我填充 end_date订阅到期的计算日期。您可以看到客户为产品 2 支付了全价，但为产品 3 获得了折扣。

customer_product
    id                      unsigned int(P)
    customer_id             unsigned int(F customer.id)
    product_id              unsigned int(F product.id)
    beg_date                date
    end_date                date
    price                   double
    renewed                 enum('f','t')
    decline_reason_id       unsigned int(F decline_reason.id)

+----+-------------+------------+------------+------------+-------+---------+-------------------+
| id | customer_id | product_id | beg_date   | end_date   | price | renewed | decline_reason_id |
+----+-------------+------------+------------+------------+-------+---------+-------------------+
|  1 |           1 |          2 | 2015-01-10 | 2016-01-10 | 25.00 |    NULL |              NULL |
|  2 |           1 |          3 | 2015-02-10 | 2018-02-10 | 75.00 |    NULL |              NULL |
|  3 |           1 |          4 | 2016-01-10 | 2017-01-10 | 28.00 |    NULL |              NULL |
| .. | ........... | .......... | .......... | .......... | ..... | ....... | ................. |    +----+-------------+------------+------------+------------+-------+---------+-------------------+

拒绝原因表。

decline_reason
    id              unsigned int(P)
    description     varchar(50)

+----+----------------+
| id | description    |
+----+----------------+
|  1 | Too expensive  |
|  2 | Didn't like it |
| .. | .............. |
+----+----------------+

customer 的所有计划表的可能已经订阅。您会注意到有两种计划 1 产品 - 第一个计划 1 在 2013 年 1 月 1 日至 2014 年 1 月 1 日期间提供，价格为 20.00 美元。下一个计划 1 于 2014 年 1 月 1 日生效，但费用为 25.00 美元。许多产品/服务的价格会随着时间的推移而上涨，这是对产品进行“版本化”的一种方式。

product
    id              unsigned int(P)
    description     varchar(255)
    term            unsigned int
    price           double
    beg_date        date
    end_date        date

+----+-------------+------+--------+------------+------------+
| id | description | term | price  | beg_date   | end_date   |
+----+-------------+------+--------+------------+------------+
|  1 | Plan 1      |    1 | 20.00  | 2013-01-01 | 2014-01-01 |
|  2 | Plan 1      |    1 | 25.00  | 2014-01-01 | 2015-02-12 |
|  3 | Plan 2      |    3 | 100.00 | 2015-01-01 | 2015-09-15 |
|  4 | Plan 3      |    1 | 35.00  | 2015-01-01 | 2017-01-01 |
| .. | ........... | .... | ...... | .......... | .......... |
+----+-------------+------+--------+------------+------------+