CODEWITHSUNDEEP
mysqldatabase-designdatabase-schemasoftware-design
Ojas Kale
Ojas Kale

Reputation: 2159

Need better database design approach

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

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

Upvotes: 1

Views: 1965

Answers (2)

Rick Lee
Rick Lee

Reputation: 763

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

Upvotes: 1

Bewilderment
Bewilderment

Reputation: 71

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.

Upvotes: 6

Related Questions