One or many databases for application for many clients in PHP

Question

I am writing a PHP application in ZF. Customers will use it to sell their products to final customers. Customers will host their application on my server or they could use their own. Most of them will host this application on my server.

I could design one database for all customers at once, so every customer will use the same database, but of course products etc. will be assigned to particular customer. Trivial.

I could use separate database for every customer, so the database structure will be simpler. I will then probably use separate subdomains and maybe even file location, but that is just a detail.

Which solution will have better performance and how big will be the difference? Which one would you choose?

Answer 1

I would use a separate database for each customer. It makes backup and scaling easier. If you ever get a large customer that needs some custom changes to the schema, you can do it easily.

If one customer needs you to restore their data, with a single database it is trivial. On a shared db, much harder.

And that if large customer ever gets a lot of traffic, you can easily put them on another server with minimal changes.

If one site gets compromised, you don't have all of teh data for everyone in one place, the damage is mitigated to just the site that was hacked.

I'd definitely recommend going with 1 db per customer if possible.

Answer 2

Personally, I would go with multiple databases - i.e. a database for each client.

As I understand it all your clients will be using just an instance of your application so these instances should have their own databases.

If you go with a single database, you are creating a great potential security risk. One client compromising the login details to the db server would automatically compromise data of all your clients.

Also a single security vulnerability (a SQL injection attack) could destroy data of all clients (with multiple dbs you could still have time to fix the security hole and release a patch before all other sites are attacked).

You don't want to have an army of 1000000 mad clients instead of just 1 angry client.

Multiple databases also give you a greater possibility of load balancing (you can have the dbs spread across more servers).