MySQL: Berechnen der verbleibenden Tage der Abonnements basierend auf Zahlungen

Question

Ich arbeite an einer Anwendung, in der wir drei verschiedene Abonnementpläne haben (nennen wir sie klein, standard, premium) jeweils mit drei verschiedenen Laufzeiten (30, 60 und 90 Tage). Jeder Benutzer kann sich für mehrere Dienste abonnieren.

Ich habe folgende Tabellen (vereinfacht):

**dienste**
id, name

**benutzer_dienste**
benutzer_id, dienst_id

**dienst_pläne**
id, dienst_id, tage, preis

**zahlungen**
id, benutzer_id, dienst_id, dienst_plan_id, betrag, erstellt_am

Ich möchte die Zahlungshistorie verwenden, um die verbleibenden Tage für jedes Dienstabonnement zu finden, stoße aber auf ein Problem, wenn ich feststelle, dass es für einen Benutzer beispielsweise möglich ist, zwei identische Dienstpläne oder Dienste zu kaufen, bevor der aktuelle abläuft.

Angenommen, der Benutzer hat zwei Dienstpläne desselben Dienstes an den folgenden Daten gekauft:

zahlungen

id | benutzer_id | dienst_id | dienst_plan_id | betrag | erstellt_am
1  | 1       | 1          | 1               | 40.00  | 2014-06-10 12:23:56
2  | 1       | 1          | 2               | 65.00  | 2014-06-15 12:27:11

Und die Dienstpläne sehen so aus (vereinfacht):

dienst_pläne

id | dienst_id | tage | preis
1  | 1          | 30   | 40.00
2  | 1          | 60   | 65.00

Angenommen, das heutige Datum ist: 2014-07-09

Dann hätte der Benutzer heute noch 1 Tag aus der ersten Zahlung übrig. Und da die andere Zahlung für denselben Dienst war (obwohl 5 Tage später), hat sie noch überhaupt keine Wirkung entfaltet, sodass die Gesamtanzahl der verbleibenden Tage 61 betragen sollte.

Ich habe darüber nachgedacht, und konnte keine ähnlichen Fragen finden. Kann jemand etwas Licht ins Dunkel bringen und eine SQL-Lösung finden? Vielleicht verwende ich das falsche Modell hier, speichere die falschen Dinge?

// Carl-David

Answer 1

Ich würde vorschlagen, dass Sie nicht genügend Daten in Ihrer Tabelle gespeichert haben.

Wie es jetzt ist, müssen Sie immer alle vorherigen Zahlungen überprüfen, um festzustellen, ob die aktuelle aktiv ist oder auf Aktivierung wartet, was zu einer großen Leistungseinbuße und komplexeren Abfragen führt.

Ein berechnete expires_at-Spalte für Zahlungen, die beim Hinzufügen einer neuen Zahlung als MAX(Zahlungen.expires_at) + INTERVAL Service_Pläne.days DAYS berechnet wird, ermöglicht es Ihnen, die verbleibenden Tage zu berechnen, indem Sie nur eine Zeile betrachten.. und ob ein Benutzer einen Plan hat oder nicht.