CODEWITHSUNDEEP
mysqlsqldatesubscription
Xyslarda
Xyslarda

Reputation: 95

MySQL: Calculating remaining days of subscriptions based on payments

I'm working on an application where we have three different subscription plans (let's call them small, standard, premium) each with three different lengths (30, 60 and 90 days). Each user can subscribe to multiple services.

I have the following tables (simplified):

**services**
id, name

**user_services**
user_id, service_id

**service_plans**
id, service_id, days, price

**payments**
id, user_id, service_id, service_plan_id, amount, created_at

I want to use the payment history to find the remaining days on each service subscription, but I'm running into a problem when I realize that it's possible for a user to for example buy two identical service plans or services before the current one expires.

Let's say that the user bought two service plans belonging to the same service at the following dates:

payments

id | user_id | service_id | service_plan_id | amount | created_at
1  | 1       | 1          | 1               | 40.00  | 2014-06-10 12:23:56
2  | 1       | 1          | 2               | 65.00  | 2014-06-15 12:27:11

And the service plans look like this (simplified):

service_plans

id | service_id | days | price
1  | 1          | 30   | 40.00
2  | 1          | 60   | 65.00

Now let's say that today's date is: 2014-07-09

Then today, the user would have 1 day remaining from the first payment. And since the other payment was for the same service (although 5 days later), it has not yet taken effect at all, so the total number of remaining days should be 61.

I've been scratching my head around this and not been able to find any similar questions. Can anyone shed some light and come up with a SQL solution? Perhaps I'm using the wrong model here, storing the wrong things?

// Carl-David

Upvotes: 2

Views: 828

Answers (1)

Arth
Arth

Reputation: 13110

I'd suggest that you haven't quite stored enough data in your table.

As it is now, you will always need to look at every previous payment to ascertain whether the current one is active or awaiting activation, a large performance hit and more complex query.

A calculated expires_at column for payments, which is worked out on the addition of a new payment as MAX(payments.expires_at) + INTERVAL service_plans.days DAYS will allow for you to work out the number of remaining days by looking at one row only.. and whether or not a user is on a plan.

Upvotes: 1

Related Questions