So I've been tasked with redesigning the database for one of our existing apps (to normalise and restructure some of the more gigantic tables) and I'm struggling to come up with a solid design for handling payments to our service.
Background Info
Our service offers various plans (Personal, Family, Business etc.) that all 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. Which I feel is where a lot of the complexity lies, in trying to combine the two.
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.
End Goal
I ideally would like to be able to track all 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).
What I have so far
My Design definitely has a long way to go for sure as it way too complex (probably more than it needs to be).
But the idea behind it all was to track all payments made (both Stripe & Invoice) then link those payments to a subscription (and a license).
Any help would be appreciated on this! :)