0

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.

Daniel Lee
  • 367
  • 5
  • 20
  • You could potentially use [relational databases](https://en.wikipedia.org/wiki/Relational_database) for this. – Daniel Aug 04 '15 at 11:48
  • I have the feeling this is a personal preference whether you want this or not. I for instance would not like the idea of dynamicly generated tables each with exactly the same structure, so I would go for a single table with a userid field. – Werring Aug 04 '15 at 11:51
  • Thanks for the responses. @Werring - I think in a lot of cases you are right. What I'm thinking here though, is if I have 100 clients, each with 200,000 rows, I would end up with a table with 200,000,000 rows. When I query that table, it would have those 20M rows to work through. If each client has their own table, then I would only need to look through 200k rows. I will never need to look up values between multiple clients at the same time. – Daniel Lee Aug 04 '15 at 12:03
  • You only need to be using indexes. – Bell Aug 04 '15 at 12:06
  • 1
    Partition your table by client instead of splitting it into multiple tables. – reaanb Aug 04 '15 at 12:29
  • @reaanb partitioning looks interesting. I haven't done this before. I must test this out and see how it goes. – Daniel Lee Aug 04 '15 at 12:56
  • Don't use `PARTITION`; it has more severe limitations, and may be slower. – Rick James Aug 04 '15 at 18:53

2 Answers2

2

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.

Nimantha
  • 6,405
  • 6
  • 28
  • 69
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks Gordon. It could possibly be thousands of clients. As I commented above, with 100 clients each with 200K rows, that is already a total of 20M rows. 1000 clients is 200M rows, which seems a lot to me (maybe I'm wrong!). – Daniel Lee Aug 04 '15 at 12:08
  • 2
    Thousands of tables is bad for performance. 200M rows is not bad, given suitable indexes. – Rick James Aug 04 '15 at 18:54
0

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.

Neville Kuyt
  • 29,247
  • 1
  • 37
  • 52