Help designing a database for SaaS subscription service
I am trying to design a database which will be for a web app that I want to sell online as a subscription service. I will be using PostgreSQL relational database technology and Stripe and Paypal for the payment system.
ERD of my tables and database - I would be interested in suggestions on how it could be improved or if I am not storing something that I should. Looking for advice from people who have run subscription based SaaS products before using Stripe/Paypal.
ERD link (pdf file):
https://drive.google.com/file/d/1iRyccFpRn1nKaFTEiGRcc72tECeOv3vS/view?usp=sharing
More information:
I've been talking to some people about what tables I need and how to split up the data and currently I have one subscriptions table that lists users that have bought plans and their plan details. I am only storing the Stripe customer id and plan id - my understanding is that all other relevant information on the customer will be accessible and stored on Stripe's website through the dashboard for my account.
I would love to hear from others who have designed a database for a subscription based SaaS product using Stripe/Paypal as their payment system and what they decided to store in their database and how they laid out their tables. I am not running an ecommerce store that sells tangible products at this point, so there is no shopping cart involved. Currently there is only one product (the app) and the subscription is time based (billed by month/year) for access to the full features.
Quick note on the tables: saved_items is simply a store of actions that the user has taken using the app in case they want to quickly repeat the same action (they are not products or a shopping cart, in other words).
Google OAuth authentication is used to create an account and login - the user does not create a password or account manually. The users_backup table was recommended to me by someone who does the same and said that sometimes the user's Google account info could change or be removed and so a monthly check and update of the users table is advised. If the account is removed from Google for some reason, the backup table will have the information stored to relate it to a subscription in case it needs to be cancelled, etc.
Thanks!
