Reputation: 315
I have User, Subscription and Payment. A Subscription can last for a week, month and 6 months. It has "duration" column in it.
Subscription table has only 3 records in it:
id, name, duration, type:
1, Subscription1, 1, week
2, Subscription2, 1, month
3, Subscription3, 6, month
A User can buy a few Subscriptions at a time, in this way once one has been used after a week or month, etc, the next -- 2nd recent paid one becomes active. Thus, if a User buys 3 Subscriptions, the 1st one becomes active and other 2 waiting.
A Payment can have different statuses such as ok, failed, pending.
I'm trying to figure out how to determine the current User's subscription without having to creating a new table UserCurrentSubscription to avoid complexity and redundancy.
How can I do that? If I take the latest Payment with the status "ok", it still might not let me infer a User's current subscription because what if a User has bought a few Subscriptions?
I just need an algorithm.
Upvotes: 0
Views: 690
Reputation: 1918
I think your best option is adding a column to the User, titled subscription_end_date
which starts with a null or 0 (depends on your preferred format) and is being updated to the new end of subscription date every time the user buys a subscription and the payment has gone through (in the same method that updates the payment's status to ok
): it adds the length of the new subscription to this field if it has a date passed the date of today. Otherwise it updates it to today+the length of the newly bought subscription.
If you don't want to add ANY field whatsoever, you could calculate it every time anew using the following, but it's very costly and I'd advise against it (I am assuming in the following that a payment has in its columns which subscription was bought, probably by having a column to hold the appropriate Subscription ID):
Go through all the ok
status payments of that user, ordered by their date, from earliest forward. For each of them:
2.1. if the payment's date precedes the date in sub_end_date then add the length of the subscription payed for to sub_end_date and update sub_end_date with the new date from that sum.
2.2. else (the date saved in sub_end_date precedes the payment's date) add the length of the subscription to the payment date and update sub_end_date with that sum.
When you've finished going through all the user's payments - if sub_end_date precedes today's date then the user does not have a valid subscription. Otherwise they do.
Upvotes: 0
Reputation: 2085
In comments you have clarified that there's a column called added_at in your Subscription table, which I presume contains a date. I'm guessing there's also a foreign key to User. Presuming that the first subscription begins on the added_at date, you could use the duration and type columns to find the end date of the earliest subscription for a user (you'll have to decide how to order the subscriptions - perhaps by the incremental id column). You could then presume the start date for the next subscription is the day after the end date of the first one, and so on.
This is an unpleasant piece of logic to be carrying out either in SQL or front end code every time you need to query this table. If possible, I would instead change the Subscription table to have start_date and end_date columns, and carry out this logic as subscriptions are inserted into the table. If subscriptions are inserted one at a time rather than in bulk, this also saves you needing to carry out any previous row/next row type logic - you can just look for the maximum end date for existing subscriptions for that user, add one day, and you now have the start date for the subscription you're inserting. If you're inserting the first subscription for a particular user, you simply insert the value for added_at into start_date as well.
Once you have start_date and end_date for each row in Subscription, checking which subscription is live on any given day is trivial.
Upvotes: 0
Reputation: 21
(This should be a comment but I lack the reputation.)
Without having a field for subscription_start_time, I don't know how this question is answerable, as the case of a user buying a month subscription followed by a week subscription is ambiguous with the case of a user buying a week subscription followed by a month subscription.
Upvotes: 1