DB Design: Subscription / Invoice Model [closed]

Question

I'm redesigning the database for one of our apps, to normalise and restructure some of the more gigantic tables.

Our service offers various plans (Personal, Family, Business etc.) that come with Monthly & Yearly subscription options.

We use Stripe as our payment gateway for Personal and Small sized business users but also offer an option to pay by invoice for our larger clients.

For every subscription, we generate a license key provided to each User attached to a specific account (that had a subscription purchased for them). So for every subscription, I'm attaching a license key to it.

I would like to track the payments and subscriptions from both payment funnels (Stripe & Manual Invoices) and to manage recurring payments (renewing subscriptions, cancelling overdue subscriptions, changing subscription plans, trials, discounts etc).

So far:

I link payments to a subscription and a license.

Ogdon

And the question is...? What issue do you foresee here? What should we help with? Quickly looking, I noticed that Purchases has a FK to Plan, yet no link in the diagram. – Halfdan

Every subscription should result in one or more invoices. Some invoices might be paid by using Stripe. Some might be paid by credit card. (An invoice is not a payment or a type of payment. An invoice is a request for payment.) – Frederiksen

Stripe's API & dashboard allows you to "pay" an invoice in Stripe "out of band" (outside of Stripe). Perhaps doing so would allow you to keep the info all in one data model (Stripe's) while not always having payments occur in Stripe itself? stripe.com/docs/billing/invoices/overview#paid – Ryswick

Answer 1

Based on the end goal you described, I would suggest considering the following model:

Here are some of the capabilities this data model provides:

• Creation of flexible tariff plans (subscription duration, number of participants, etc.).
• Changing tariff plans for a subscription.
• Editing payment sources for debiting an account.
• Change the payment source for a specific subscription.
• Tracking payment history (how much, when, for what).
• Change in price in the tariff plan.
• Renew subscriptions (if is auto-renewable=true) by making automatic payments from the payment source linked to the subscription in accordance with the tariff plan.
• Based on the data, you can send notifications that a subscription will be charged. Or, for example, invoices on the basis of which it will be necessary to top up the account balance (if the corresponding payment source is selected).
• And so on and so forth.

Expiatory