title: Database design for subscription billing
body: <p>Looking for some guidance on the basic design of the database for a recurring billing system.</p>
  
  <p>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:</p>
  
  <pre><code>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 |
  +----+--------+------------+------------+-------+--------------+-------+
  </code></pre>
  
  <p>It has to support the following use cases:</p>
  
  <ol>
  <li>Subscriptions are for a year or 3 years</li>
  <li>A customer subscribes to a product plan</li>
  <li>A customer can subscribe to multiple products</li>
  <li>Add-ons for the product can be included in the    subscription</li>
  <li>An add-on can be added part way through a subscription</li>
  <li>An add-on can be added as a trial for a period of time  during a subscription</li>
  <li>Some subscriptions may be a reduced rate (e.g. agreed a second subscription for free due to a special circumstance)</li>
  <li>At renewal the plan, add-ons and price may change</li>
  <li>Ability to record a reason for not renewing</li>
  <li><p>Full history should be visible for any customer, for example in the above database you can see customer 10:</p>
  
  <ul>
  <li>Joined 2012-09-04</li>
  <li>Added an add-on to subscription on 2013-04-01 after one month trial</li>
  <li>Didn't renew as too expensive, so expired on 2014-09-03</li>
  <li>Subscribed again on 2015-10-14 at a higher price, with payment outstanding</li>
  </ul></li>
  </ol>
  
  <p>Any pointers?</p>