2

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

Strawberry
  • 33,750
  • 13
  • 40
  • 57
Xyslarda
  • 95
  • 1
  • 3
  • 9
  • 1
    At the moment, this question is unclear. If you like, consider following this simple two-step course of action: 1. If you've not already done so, provide proper DDLs (and/or an sqlfiddle) so that we can more easily replicate the problem. 2. If you've not already done so, provide a desired result set that corresponds with the information provided in step 1. – Strawberry Jul 10 '14 at 09:14
  • Also, can a payment amount ever be less (or more) than its related service plan price? If so, what happens under these circumstances - or is payment.amount not relevant to this problem (in which case consider removing it from the question) – Strawberry Jul 10 '14 at 09:17
  • Could there be (for example) 10 payments made on the same day? Ie, in your example could user_id 1 have made repeated payments on 2014/06/15? If so I am struggling to think of a way of doing this without repeated queries. If so then denormalising it a bit so you can store a cumulative end date similar to @FuzzyTree suggestion might be best. – Kickstart Jul 10 '14 at 09:19
  • @Strawberry: I cannot provide any SQL since that's what I'm asking for. Amount is relevant since the prices might change. – Xyslarda Jul 10 '14 at 10:02
  • @Kickstart: Yes, theoretically. – Xyslarda Jul 10 '14 at 10:03
  • @Calle: Strawberry means just the DDL and the expected results; [SQL fiddle](http://sqlfiddle.com/#!2/0843c). Feel free to add the link to your qu. – Arth Jul 10 '14 at 10:22
  • Probably, you should limit things so you can only have one "pending" (not current) plan, which would help some. (It would also make things easier when you have to give refunds.) You need to be storing the history of the _prices_, separately from the record of how much was paid (ie, add a "takeEffectOn` date/timestamp column to `service_plans`). Technically, `user_services` is derived, although materializing it would probably bring performance benefits. Here you probably need some sort of running sum... which would be easier in an RDBMS with window functions. – Clockwork-Muse Jul 10 '14 at 14:05

1 Answers1

1

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.

Arth
  • 12,789
  • 5
  • 37
  • 69