Sarkis Arutiunian Sarkis Arutiunian - 1 month ago 10
MySQL Question

organizing multi-tenant db/MySQL [SaaS]

Good example will be shopify. Where you have N number of users (in this case each user assume site). And each user will have it's own records in DB. But db schema will be the same (same tables for each user,

products
,
customers
,
orders
etc.).

So question is what will be the best way to organize this kind of solution?
Store everything in one DB but in a different tables, or run separate DB for each user (but then will be question with maintaining, scalability and automatization)

possible solution:



We can use one DB with common tables like
products
,
customers
,
orders
etc. And we will have table
users
where we store records about each site.
In tables
products
,
customers
we will group all records by
user_id
.
This is one of possible solutions. But if we will have 1000 users (sites), each will have ~2k products, and ~100k customers, we can end up with tables which has millions of records, so questions will be:


  • how it will perform compare to each user (site) would have it's own DB?

  • how reliable this approach? bigger data, harder maintain, backup/restore

  • safety, if something wrong with one source thousands will be affected



Any links etc. will be much appreciated, thanks!

Answer Source

I recommend reviewing databases by well-supported open source solutions. With this in mind, here's a pretty simple schema I found real quick that'd explain a good working solution for this with scale-ability in mind.

http://www.zentut.com/sql-tutorial/sql-sample-database/