500 databases or 1 database with 500 tables or just 1 table with all the records? [duplicate]

Question

I currently have an application that is used by a single end customer. For ease of discussion assume the application only needs a single database table for all records. I now need to support multi-tenancy and so in the future I will have about 500 customers using the same database server.

1) A possible solution is to have a separate database for each customer. As there will be around 500 customers that means having MySQL/SQL Server/Oracle/etc... host 500 separate databases. This sounds like overkill for a database server running on an average cloud based server.

2) Another solution is to stick with the single database I already have but to have 500 different tables, one for each end customer. Each customer only averages around 2000 records in their own table. This is easy to implement and I would guess makes it easy to migrate the customer to another server, just move the entire table data over.

3) Lastly I could stick to using a single table in the single database. Instead I add an additional column that identifies the customer the record belongs to. But then the table ends up with about 1,000,000 records which is the aggregate of all 500 customers that average 2,000 each.

I do not know enough about performance and scaling to know 1, 2, 3 is going to give the best performance. Any ideas?

Answer 1

Personally, I'd go with option 3 because:

• It is normalized and simple
• Easy to query for reports
• Easy to back up(just 1 database to worry about)
• If you index the table well, performance shouldn't be an issue

Also, Performance aside, here are some reasons why you would want to avoid option 1 and 2.

Cons if you go with 500 databases, 1 per each customer:

• Database Backups will be annoyingly tedious and messy
• Cross Database queries are normally a luxury of enterprise database servers, in other words, generating customer reports will be a pain because you can't just have a single query to pull all the relevant customer information in your database
• Inelegant

Cons if you go with 500 tables in 1 database, 1 table per customer:

• Your database is horribly unnormalized, lots column information duplication going on everywhere
• While it is easier to query a lot of tables than to query from different databases, you'd still have to run the same query across god knows how many tables instead of just 1 query

Answer 2

There's more to evaluate than just performance when considering multitenancy. I'd recommend reading over this MSDN article for more details.

Broadly speaking, you need to consider these factors before you settle on a particular approach.

• System administration costs
• Database server costs (aka hardware)
• Database licensing costs (aka software)
• Complexities involved in writing code
• Complexities involved in maintaining code
• Validating security and environment separation
• Expected Performance and SLAs
• Per client and aggregated data usage

And as you noted, there's essentially three approaches to consider:

1. Dedicated databases for each client
2. Shared database, separate schemas
3. Shared database, shared schema

And a quick summary of pros and cons:

Dedicated database for each client

Higher costs across the board, but simpler code and easier to validate on the security side. Trivial to upgrade a particular environment as a client's data usage increases.

Shared database, separate schemas

Decreased costs as compared to the dedicated database approach, but at the trade-off of increased complexity in the code. And you'll need to be more diligent to make sure the schemas remain separate. You'll potentially have more repetitive code here since you'll sometimes have to copy / paste things on a per-client basis. Again, relatively trivial to migrate a client if their data usage justifies an upgrade.

Shared database, shared schema

Also has decreased costs, but code complexity becomes greater in order to make sure that things remain secure. You run a higher risk of inadvertent data exposure if you make a mistake in the security access layer. On the other hand, you'll have less repetitive code as you're not duplicating things across schemas. A downside is that this approach has the most complicated migration path when a client's data usage exceeds the existing environment's abilities.

---

The second two approaches are what I have seen most commonly used due to the decrease in costs.
Organizations that have clients with widely different data usage tend to use the second approach.
Organizations with consistent and lower data usage lean towards the third.

Answer 3

IMHO it will make a difference if you design a new application from scratch with multi-tenancy in mind, or if you are going to use a complex, existing application which was not designed for multi-tenancy.

For the first situation, the "one DB & one table" approach will probably be the best, as the others wrote. For the second, adding multi-tenancy to the application afterwards can be so hard that using different databases can be indeed the better alternative, since it avoids the need to change anything in your application. Of course, "different databases" results in a different overhead when using an Oracle system compared to an MS SQL server system or a MySQL server. In an Oracle System, there is also the option of "different schemas", and from Oracle 12c, there is a specific "Multitenant" option allowing you to create a container DB holding lots of "pluggable databases".

As long as you have really only one table, I expect your application not be so complex that multi-tenancy could not be implemented afterwards, but you wrote

For ease of discussion assume the application only needs a single database table for all records.

so I guess the real thing does have more tables, and you have to decide how hard it is to make the relevant changes to your application.

Answer 4

I guess part of it comes down to personal preferences.

If you handle multiple customers in the same application (i.e. one program receiving requests from multiple customers) I would prefer option 3. Handling multiple customers in this case is part of the application logic, and designed into the data model.

If you run a separate instance of the application for each customer I would initially tend to option 1, since has the best isolation between the customers.

However, with several hundred customers option 1 becomes rather cumbersome since it doesn't scale well, and option 2 is a good compromise.

Answer 5

Number 1 is best performance, number 3 is worst. But really, as many other answers have stated - that is the last thing you need to be worried about, specially with the amount of data you posted in your question.

The things I would be worried about:

• Who owns the data and how proprietary is it?
• Are there realistically going to be schema changes and/or different live versions?
• What type of maintenance will you need to do?
• Any inter-client aggregation you need?
• What's the budget here?
• Are there any legal requirements? Can there be in the future?

To expand on each of these:

Who owns the data and how proprietary is it?

If this is your data, then this should not be an issue. However, if this is corporate data of the client, or any kind of personal data, or data that absolutely must not leak - number 3 is out of the question. Unless you waive in some magic to make only a subset of rows unreadable, you're allowing any client to access any other clients data. Your customers might not appreciate this. In fact - if you're a juicy target, then simply execution time or table stats (such as number of rows) already give more information than you may want to expose.

Number 2 is OK, as long as you get your permissions and users right.

Are there realistically going to be schema changes and/or different live versions?

The answer to this question is - of course there are, sooner or later. Unless you just have 1 text field in your DB where you put everything.

There are ways to change the schema gracefully - make sure the new version of the code works with both the old and new schema version, or vice-versa. You will also need to make sure all clients are updated timely. However this also means that you either need to force update the client, or span your schema change across 2 major updates.

How the above relates to your situation? Well, in number 3, you have to synchronize all updates of all clients. That is a nightmare to say the least. Numbers 1 and 2 are easier in this sense, since you can do problematic clients one-by-one without turning them off.

Number 3 is also hell in case one of the clients requests an older version of the app, because the new one doesn't suit them.

What type of maintenance will you need to do?

Backup is the first thing that comes to mind. Here option 3 outshines everything - just dump the table and you're done! Number 2 is OK - a full DB backup is not too difficult. Number 1 is bad - you have to setup a backup on EACH DB. It's incredibly easy to forget or fudge something. However providing a client a backup of their data on request is trivial in 1 and 2, but a bit involved in 3.

How often will you need to change queries to the DB? For number 3 they will be more complicated - or rather - they're as simple, but they have an incredible number of places you can screw up because you forgot the AND clientId = :clientId.

Any inter-client aggregation you need?

If the case of 1, you better hope you've got a good team of developers and an enterprise server. There is no way to do this that is both easy, reliable and cost-effective.

Number 2 is OK as long as you can generate your queries properly.

Number 3 is easiest.

You'd have to pose the question to your analytics department (if you have one).

What's the budget here?

Lots of DBs may need lots of licenses. And lots of open ports and possibly virtual machines. This depends on what exactly are you using and what hosting is it.

Are there any legal requirements? Can there be in the future?

There are sometimes legal requirements on where data can physically reside. If all your clients are from one country that shouldn't be a problem, but should you go world-wide it may. This is especially applicable to financial and personal data.

A question not mentioned here - are there requirements on latency for the DB? Your Australian or Russian client may not be very happy about their queries going all the way to NY (say).

Another one is resilience and DDoS - physically separated services for separate clients is just that much more difficult to take down.

TL;DR

All in all - I would say if you are using this as a small storage mostly for non-proprietary and non-critical data, and don't expect much growth (as in the amount of data, not company growth) or change in formats - use Number 3. Less hassle, less cost, less admins involved. If this is going to grow into proper datastores with possibly touchy stuff in them - use number 1. Number 2 is somewhere in between.

Answer 6

Use all three. One size does not fit all.

The shared model/shared server could be used for small implementations or for those customers who want the least amount of data security. Probably need application chanages for this one.

Cost: $

The exclusive model/shared server allows for increases data segregation but client share the same resources (server).

Cost: $$$

The exclusive model/exclusive model allows for a unique silo.

Cost: $$$$$

This model allows clients to pick and choose the best model that suits them (depending on size and security concerns). Just need to figure out how much to charge for each to keep profitable.

Answer 7

All things equal, option #3 - all in one table (or set of tables).

In addition to some of the other reasons provided - easier maintenance and performance shouldn't be noticeably different if correctly indexed - there's also the issue of future proofing.

Consider the migration of data. If you go with option 3, then in the future, its straight forward to migrate that to 1 or 2 if you really need to. For example, if a customer ends up needing significant customizations such that they then need their own database. You can just move their data into a new table, keeping ID's all the same. On the other hand, it would be much tougher to go from 1 or 2 to option 3 if you discover reasons you need that data to be shared. Its always easier to split data than combine it.