Good practices for designing monthly subscription system in database [closed]

I would use this model

Your clients

Client------Client IDName...

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 IDNameCredits_per_monthPrice_per_month(Price_per_year)

Your subscriptions

Subscriptions------Subscription IDClient IDPlan IDSubscription_start_timestampSubscription_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: 30ID: 2; Name: Premium 1st month offer; Credits: -1; Price_per_month: 0Subscription------ID: 1, Client ID: 1, Plan ID: 2, Start: 2014-05-07 08:00, End: 2014-06-06 07:59ID: 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:59ID: 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 clientInner Join Subscription sub On sub.client_id = client.idWhere 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.

Looking at the accepted answer I would add another table where all the changes or updates of a subscription or plan would be stored for back reference. This way you have a clear log on when and what plans were selected for what period.

I would make sure the subscription table would contain the variables which tells you if it is active or not, and to what date it is payed for and any other possible data you would require in daily operations.

The extra table will make sure of you being able to recreate any subscription at any given time if needed.

I would use a relational table design and a relational database.

I would have a Client table.

Client------Client IDClient Last NameClient First name...

I would have a Subscription table

Subscription------------Subscription IDClient IDSubscription Purchased Time stampSubscription Started Time stampSubscription Ends Time stamp

I would have a Purchase table

Purchase--------Purchase IDSubscription IDPayment method...

Now to answer your questions. You should ask just one question at a time.

Which one should be a row in the DB: the month_subscription (ie 1 row per client per month) or the subscription itself (ie 1 row per client)?

One row per subscription per month.

How would you handle the automatic renewal of the monthly subscription?

Netflix just debits my PayPal account every month. You could do the same with PayPal or with a credit card. If you accept credit cards, you're going to have to make an arrangement with your bank, a credit card processor, or PayPal.

How would you handle payments designing if you foresee to use services like PayPal to handle automatic monthly payments?

Netflix just debits my PayPal account every month. You could do the same.