Single V/s Multiple Databases

Question

I've built this web app (php & mysql) which stores information for various organisations (about 20 clients currently).

The current scenario stores client-related information in individual databases, so there's 20 client databases and 1 master database.

One of the main advantages here is that as each client db is isolated, the numbering of client artifacts (reports, audits) etc. is sequenced; giving our clients a feeling of security.

Each DB has roughly 15 tables, and the most rows in a table are about 2000. This is expected to be bumped upto 5000 records, at most.

Managing a single db-level change means changing 20 databases, but in the rare event that I need to make such a change, I use a script that does this in a single function call.

We're on a shared hosting arrangement, and our ISP supplies us with a limited no. of databases; and that's what led me to think in terms of centralising the database; so that ALL the client data can be stored in the master database.

Of course, some important issues that crop up are:

a. Maintaining the artifact sequence, (this could be addressed by creating an additional reference key) b. Speed and performance (in which case I can create indexes to speed things up) c. Security: This will be managed as each query that fetches client info. will also track their client_id

In the future, we might need to consider comparing datasets of one organisation with another, but I believe that can be achieved on a centralised db too. I'm somewhat inclined (for performance and maintainability reasons) to move to a centralised database.

Do you think moving to a centralised database makes more sense than staying as we are (on individual databases)?

Thanks for your advice.

Answer 1

I'd have separate database for separate clients. A client might demand this for security reasons - i.e. only their site has access to their data. It also means that if a client want to move their data then it's going to be much easier to manage.

It also means that if there is a problem with one client's database it doesn't affect all of the others.

If you want to compare data between clients then you should do that separately.

If you are running out of databases that you can have then perhaps you should be considering changing your host provider.

Answer 2

There are inherit risks and rewards to both system. I worked for a financial firm that supported roughly 40 clients (national banks) on 1 database. We then purchased another company that sold similar software and that had gone with 1 database per client. Finally, the company went bankrupt and we did have to export all user data. Here is what the people I worked with and I found:

Pro's of Single DB:

1. Software updates and bug fixes are easier.
2. Easy to manage and report on all client data.
3. Updating data becomes easier.
4. Easy to create modular functionality that 1 client wants, turn if off for the other clients, and then turn it one when they desire it in the future.

Con's of Single DB:

1. Data integrity - We had 2 or 3 cases where 1 bank's users saw another bank's data. This was a nightmare. Especially because the site users were not just the bank employees but actual account holding customers of the bank! This is by far the biggest issue with 1 database
2. Exporting client data -When we had to this it usually wasn't a big deal. You end up with 1 table that has all of the clients in it and you key off of that table to get your client specific data.

Pro's of Multiple DBs:

1. There is no concern of cross client data contamination or breaches
2. Exporting a clients data is dead easy.

Con's of Multiple DBs:

1. Updates and bug fixes - This was the real nightmare. When you have 20 clients on 20 different databases you quickly run into a the case where 1 client wants a bug fixed and another thinks the bug is a feature or doesn't want to risk the update. Furthermore, you will have instances where 1 client wants a game changing enhancement but the other clients don't. When this happens you databases will start to diverge. Suddenly you will have to update clients 1-15 with 1 script 16-19 with another, and 20 with a third. We saw this become such an issue that a bug fix would take 15 to 20 times as long for the company we purchased than for us because they had to run all tests for every client and deal with each clients special code. Effectively, they needed a new support person for every new client, whereas the parent company needed one for every 5 to 10 clients.
2. DB management - When you get to a large number of clients managing all of the databases becomes a real hassle. You will without a doubt need more DBA time to manage them.

In the end my recommendation having seen and done both is to have "discipline"! I think the multi-db choice is slightly better because it protects you but you cannot ever let you clients make a choice that causes you to add functionality to only them or you will be putting yourself on a path to failure.

Answer 3

First the artifact sequencing. I assume you are using integer primary keys to provide this. Really you should have a separate "artifact number" column. PK's should be PK's and nothing else. People talk about "natural keys" and the like and I cringe. Whenever you rely on the PK to be more than an identifier it comes back to bite you. If you want to know the sequence of something store a date or a sequence number.

I think in your case configuration management would drive you to a single database. Look at what it costs you in time to maintain and upgrade databases. What costs are associated with each release of the software? Also think about the cost when you get a new customer and have to create a DB and configure the app for it. Anything can be automated, the question is, will it be worth it when you have 100 databases?

Down the road, it is easier to scale (partitioning, hardware, sharding etc.) a single database than it is to do the same for 100 databases.

I think the other posters have made some excellent points so I wont go over those.

Answer 4

To add to the pro's / con's listed up to now:

Pro's of multiple databases:

1. Locking issues are avoided; we've got databases where the clients can trigger DDL-changes on some of the tables. For the larger tables (>2m records) this locks the table for a considerable amount of time. The only people at a disadvantage are their own users, so this is sort-of acceptable.

2. Flexibility - some clients have specific wishes regarding data they wish to store; multi-database allowed us the flexibility to alter their database specifically, without having to clutter the data model for the other clients.

Cons:

1. Major con: Joining on other tables is a lot more cumbersome. We've got a Main database which contains most meta-data. The client-specific database users don't have access to this database, so all joins between tables in that database and the client-specific one are handled in the application instead of in the database. You could resolve this by giving the client-specific users access to the main database, but then the app could/might leak information again.

Good luck in choosing!

Answer 5

The only reason I wouldn't have an individual database for each client is if you're going to have 100s or 1000s of clients/databases. This could get really hairy to manage including making database changes or doing something across all databases. Actions that occur across large numbers of multiple databases could also be slow as you need to open (and therefore close) so many tables.

But aside from this case, I think multiple databases is better.

One advantage, which may not be important but can be useful, is that each client gets their own sequential IDs (instead of possibly skipping a bunch because another client(s) added records).

Also, multiple databases allows for the sub tables (like phone type) to be easily customizable per client without the need for a parent record id in these tables as well.

Answer 6

I know you have already chosen an answer, but it looks like there is another solution that wasn't suggested:

Move everything to one database, but create tables for each customer, using a prefix, like this:

initec_contacts_tbl
initec_accounts_tbl
initec_personel_tbl
...
masterco_contacts_tbl
masterco_accounts_tbl
masterco_personel_tbl

It's kind of the best of both worlds.

• It is very easy to migrate from your current setup to the new set up.
• You can create 1 user per client and restrict its privileges to the tables of his company and nothing else
• You can create a superuser and easily aggregate data if you need to do so.
• Use only one database