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:
In addition of database design, there could be triggers that need to be set to do this (?).
My pain points:
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.
Bad Practice No. Designing a database is not a deterministic task; two database designers may follow all the rules and normalization principles for a given problem, and in most cases they will generate different data layouts. This is inherent to the creative nature of software engineering.
Subscription databases are those that are acquired by purchase or annual contract. The institution that subscribes to a database service must comply with license agreements.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With