Good practices for designing monthly subscription system in database

Question

I'd like to know how to design a monthly subscription software system in a database. These systems are broadly used all over internet, though I can't find lots of stuff about database design.

In my case, these elements (and maybe some others I forgot) have to be included:

• Client
• Plan (like 'basic'/'premium'). Each plan has a monthly price, and an amount of credits (for instance: basic plan provides 30 credits per month and premium plans unlimited credits).
• Credits are a virtual money that is spent within the application.
• Subscription/Unsubscriptions
• Payments (Note that the price actually paid could be different from the base price of the plan because of discount, etc.)
• ... ?

In addition of database design, there could be triggers that need to be set to do this (?).

My pain points:

• I can't see in a general manner what is the global design of this
• Which one should be a row in the DB: the month_susbscrition (ie 1 row per client per month) or the subscription itself (ie 1 row per client)?
• How would you handle the automatic renewal of the monthly subscription?
• How would you handle payments designing if you foresee to use services like Paypal to handle automatic monthly payments?

Note

I voluntarily don't expose my needs in details because that way, the debate can stay generic and can be more useful to other people.

Thanks for help.

Answer 1

I would use this model

Your clients

Client
------
Client ID
Name
...

Your plans (you can define new plans when you want). I add a Price_per_year if you want to propose a discount if the client buys 12 months in one shot (but it's only an idea).

Plan
------
Plan ID
Name
Credits_per_month
Price_per_month
(Price_per_year)

Your subscriptions

Subscriptions
------
Subscription ID
Client ID
Plan ID
Subscription_start_timestamp
Subscription_end_timestamp

Considering this model I would use 1 row per client per plan.

When a client subscribes to an offer like "Premium with 1st month free !", your database would look like this :

Client
------
ID: 1; LastName: Foo; ...

Plan
------
ID: 1; Name: Premium; Credits: -1 (unlimited); Price_per_month: 30
ID: 2; Name: Premium 1st month offer; Credits: -1; Price_per_month: 0

Subscription
------
ID: 1, Client ID: 1, Plan ID: 2, Start: 2014-05-07 08:00, End: 2014-06-06 07:59
ID: 1, Client ID: 1, Plan ID: 1, Start: 2014-06-07 08:00, End: 9999-12-06 07:59

When a client unsubscribe the 1st July, update the column end in your Subscription table with the month and the year only (because you have pre-set the day and the time).

Subscription
------
ID: 1, Client ID: 1, Plan ID: 2, Start: 2014-05-07 08:00, End: 2014-06-06 07:59
ID: 1, Client ID: 1, Plan ID: 1, Start: 2014-06-07 08:00, End: 2014-07-06 07:59

To know if a client is not unsubscribe, you could use this :

Select Count(client.*) From Client client
Inner Join Subscription sub On sub.client_id = client.id
Where DATE_TODAY Between sub.start And sub.end

Make sure that you can't have 2 subscriptions for a client at the same time.

This allow you to handle automatically the monthly subscription in your app, but not with your bank/paypal account.

But some banks offer you two services: - Unique debit - Periodic debit

The second one would allow you to handle the monthly subscription.