One or many databases for application for many clients in PHP

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 d开发者_Go百科atabase, 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?

---

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.

---

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).

---

Performance wise you're basically start with a 'sharding' approach. Because of this, the sharding performance strategy will be piece of cake.

The downside is that you could argue you're losing some (undefined) bit of overhead in the duplication.

One pitfall is that you might not notice performance issues in major components as quickly. This is because they are so scattered, so they might not be visible on your radar. Load testing is the way to get ahead of this.

---

To some extent this is a question of personal opinion. There are pros and cons of both models.

Personally, and because of the "they could use their own" comment, I would go with a seperate database per customer. This gives you

• The ability to move customer data around when necessary. For example moving a single customer onto a different servers/setups depending on things like load.
• If something goes wrong you only impact one customer and not everybody.
• You can spread DB load across multiple DB servers if necessary.
• If a customer comes to you with a specific requirement you can more easily cater for this without impact other customers.

From a performance perspective, to be honest I don't think there is any real performace gain in either model. That said this does of course depend on the structure of your DB and the hardware it runs on.

---

Don't choose multiple databases solution, if your needs can be fulfilled with one database. Because multiple databases will lead to big burden in long run, and your system will become highly complicated and unmanageable as you grow.

Using proper relationship you can go long way

A Client model can have many Products // why multiple databases?

Performance can achieved in either ways, just going multiple dbs will NOT benefit in that direction