I have designed a database schema for a subscription product. user can select subscription starting from a date for certain amount of days. User can cancel a subscription for some days while still keeping the subscription active afterwards. meaning if user subscribes for a month she can cancel it on days say 10,15 and 20 thus paying for only 27 days (30 minus 3).
So far I have come up with this schema. enter image description here

  • each user has one profile.
  • user can select a plan.
  • once user selects a plan it is noted as transaction which also stores information about start date and duration of that plan.
  • each transaction has payment (focusing on that part later)

Now, since user can cancel subscription any day how should I keep track of different users and days on which they had subscription?

The solution that I have in mind is crate a new table Plan_Transaction_user which will keep track of each date and transaction ID for that date. This way If user cancels her subscription on particular date there will be no record of that date for that transaction ID.

table will look like this:

Date       Transaction ID       
1-1-2017   1
1-1-2017   2
1-1-2017   3
1-2-2017   1
1-2-2017   3

Since user associated with transaction_id 2 cancelled for day 2 her transaction record is not present in this table.

Now if I have customer base of say 5000 then in best case within one year I will have 5000 * 365 ~ 1.8m rows. I am sure this is not best approach to go about it. Could you please suggest me any better schema or some changes in existing schema which can be more efficient? Just in case you want to know I will be using MariaDB (AWS RDS) as a database and Python 2 as my language.

Thank you, Ojas

  • 2
    Tracking individual dates seems really broken here. You proably want to capture bounded ranges, like start and end dates. – tadman Feb 23 '17 at 3:00
  • if I only keep track of start and end, How would I query "on particular day how many subscribers were there?" @tadman – Ojas Kale Feb 23 '17 at 3:04
  • 2
    You can use the BETWEEN operator to test. – tadman Feb 23 '17 at 3:50
  • 1
    Please edit your title to describe the core of your question, rather than a generic phrase – Basil Bourque Feb 23 '17 at 4:13

You can add a end_date field in Transaction table instead of duration. You can easily defined end_date as start_date + how many days you will given for selected plan. When user cancel some days then you can reduce end_date as end_date = end_date - number of cancel days. You can check how many valid subscription currently at any days checking through end_date >= today.


Similar to your Plan_Transaction_user design, if u only need to know how many subscribers for any particular date, but not who they are, u can aggregate the table by day. Like

Date       user_count      
1-1-2017   1
1-2-2017   2

Your Answer

