Database Design for Subscription

Question

Look for some guidelines for a basic database design for a recurring billing system.

My project has one table for storing the next subscription cycle (either on the same or on a new plan, the same or different price, or not updated), and the other on the saved subscription (which products were purchased, when, at what price ) This is what I have:

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 |
+----+--------+------------+------------+-------+--------------+-------+

It should support the following use cases:

• Subscriptions for a year or 3 years
• Customer subscribes to a product plan
• Customer can subscribe to multiple products
• Product add-ons may be included in the subscription.
• Add-in can be added partially through subscription
• An add-in can be added as a trial during the subscription time period.
• (, - ).
• , .

• , , 10:

  • 2012-09-04
  • ​​ 2013-04-01
  • , 2014-09-03
  • 2015-10-14

?

Answer 1

, . , , , . , . , beg_date end_date , product.

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...

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 , . 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 . , Plan 1 - 1 1 2013 1 2014 20,00 . 1 1 2014 , 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 |
| .. | ........... | .... | ...... | .......... | .......... |
+----+-------------+------+--------+------------+------------+