Database design for subscription billing

Question

Looking for some guidance on the basic design of the database for a recurring billing system.

The design I've come up with has one table to store the next cycle of the subscription (either on the same or new plan, same or different price, or not renewing), and another to store the the applied subscription (what products were bought when, at what price). This is what I've got:

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 has to support the following use cases:

1. Subscriptions are for a year or 3 years
2. A customer subscribes to a product plan
3. A customer can subscribe to multiple products
4. Add-ons for the product can be included in the subscription
5. An add-on can be added part way through a subscription
6. An add-on can be added as a trial for a period of time during a subscription
7. Some subscriptions may be a reduced rate (e.g. agreed a second subscription for free due to a special circumstance)
8. At renewal the plan, add-ons and price may change
9. Ability to record a reason for not renewing

10. Full history should be visible for any customer, for example in the above database you can see customer 10:

    • Joined 2012-09-04
    • Added an add-on to subscription on 2013-04-01 after one month trial
    • Didn't renew as too expensive, so expired on 2014-09-03
    • Subscribed again on 2015-10-14 at a higher price, with payment outstanding

Any pointers?

Answer 1

This is a table containing your addons. You didn't explicitly say your addons cost money but you allude to that so I've included a price. I've also assumed that addons are tied to particular products. If your addons change over time I'd have a beg_date and end_date in this table just like in the 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 |
| .. | .......... | ............... | ..... |
+----+------------+-----------------+-------+

Just a regular old customer table...

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

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

Here's all the addon's every customer has ever purchased or trialed. In this example end_date defaults to NULL and won't have a value in it until the customer stops using the addon. Alternately you could fill in the end_date based on when the associated product is set to expire. Notice the customer paid full price for addon 1, nothing for addon 2 (because they just trialed it) and they got addon 3 at a discounted rate.

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

Here's all the product's every customer has ever purchased. In this example I'm populating end_date with the calculated date the subscription should expire. You can see the customer paid full price for Product 2 but got a discount for Product 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 |
| .. | ........... | .......... | .......... | .......... | ..... | ....... | ................. |    +----+-------------+------------+------------+------------+-------+---------+-------------------+

A table of decline reasons.

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

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

A table of all the plans to which customer's could have subscribed. You'll note there are two Plan 1 products - the first Plan 1 was offered from January 1, 2013 to January 1, 2014 and was $20.00. The next Plan 1 became effective January 1, 2014 but cost $25.00. Many products/services go up in price over time, this is one way to "version" your products.

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