Help with designing database for SAAS app

Project Feedback
#1

Hi guys, I’m currently in development for a full stack saas application and would like some advice on the database design.

The app is a management app for a family member’s cleaning business to help pool their client scheduling information all in one location. The front end is react, the backend is node.js, and the database is mysql. The idea right now is to create the app with the intention of scaling out to multiple businesses.

The current problem I’m facing is how I’ll keep the data isolated in mysql from multiple users managing their own client data. My thought is to create a user table and FK the client table to that specific user when they user creates new clients. What do you guys think about this? And would there be a better way to handle this given the intention to scale out in the future? Any help would be awesome thank you!

#2

You should have a look at the basic of database design and think deeply about the entities and relationships.

E.g.:

  • Can a client belong to multiple users?
  • Can a client belong to themself? (I clean my own house)
  • Can a client be a user? (I clean houses (user) and I want someone to clean my house (client).)

Mistakes in the database design phase are annoying, so better invest more time into asking all questions!

#3

Thank you for your reply! @miku86

Thats a lot of help with those example questions to ask myself. Do you have any recommendations on resources to use to learn basic database design? I’m currently looking at essentialsql.com going through their database normalization right now.

#4

If I understand your question correct, there are three main multi-tenancy model types.

  1. Single database with multiple schemas.
  2. Multi-tenant architecture hosts data in multiple databases.
  3. A single, shared database with a multi-tenant database.

Option 2 is the safest IMHO.