CODEWITHSUNDEEP
phpmysqldatabasedatabase-design
Daniel Lee
Daniel Lee

Reputation: 367

MySQL - One table of each client or Single table with all clients

I am beginning a project where there will be multiple clients. In the database, each client can potentially have hundreds of thousands of rows.

Instead of having all of the clients in a single table, would it make sense to split the clients so that each has their own table? To me this makes sense since each time you query for a client, you would only be looking up a table with their data.

Upvotes: 0

Views: 1479

Answers (2)

Neville Kuyt
Neville Kuyt

Reputation: 29629

This seems to be about multi tenancy - there are several SO answers on this topic.

It's a multi-dimensional trade-off.

If you have "hundreds or thousands" of clients, those clients presumably have related entities - orders, contact details, etc. So you're almost certainly not just considering have many "client" tables, but essentially many (logically and/or physically) separate databases with the full schema to support your business domain.

That's a big decision - you now have to figure out how to deploy thousands of instances of your software, support thousands of environments (even if they are logical, rather than physical), make sure each of them is configured properly etc. The good news is that this may make security and provisioning easier, and it should allow you to partition clients to ensure high performance.

That's a big decision - I'd say it's an order of magnitude harder than building an app with a single database. I'd really only want to make that decision if I were 100% certain it's necessary. Modern databases can easily query across very large datasets as long as you can use indexes; hundreds of millions of rows are not particularly scary.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269623

In general, it is better to have a single database with all the data in shared tables. This is better for numerous reasons:

  • You have only one system to maintain and update.
  • You can perform queries that are cross-client ("How many clients modified the data yesterday?")
  • SQL is more optimal with larger tables versus lots of small tables.

There are some good reasons why you would want to split client data among different tables, or even different databases or different servers. These considerations include:

  • System requirements that require the physical separation of data.
  • Different development paths for different clients.
  • Difficult to implement security requirements (security is harder to implement at the row level).

In such a case, I would go for different databases, rather than just different tables within a single database.

Hundreds of thousands of rows per client is not very big by SQL standards, unless you start having thousands of clients.

Upvotes: 2

Related Questions