Database design for subscription billing
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:
- Subscriptions are for a year or 3 years
- A customer subscribes to a product plan
- A customer can subscribe to multiple products
- Add-ons for the product can be included in the subscription
- An add-on can be added part way through a subscription
- An add-on can be added as a trial for a period of time during a subscription
- Some subscriptions may be a reduced rate (e.g. agreed a second subscription for free due to a special circumstance)
- At renewal the plan, add-ons and price may change
- Ability to record a reason for not renewing
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
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 |
| .. | ........... | .... | ...... | .......... | .......... |
+----+-------------+------+--------+------------+------------+