We are just starting to develop a web app and have a similar problem to solve (lots of customers with their own isolated data).
What problems should I expect with using a database per customer? How can I solve them?
My Initial Thoughts
Advantages of a database per customer
- Simpler database schema
- Simpler backups - you can backup each customer in turn without it really impacting on other customers.
- Makes it easy to export a given customers data.
- Better cache performance - a write to one of the more active tables only impacts that single customer that performed the write.
- Easier to scale across hardware. For example, when we need to go from 1 to 2 servers, we just move half our customers to the new server.
- Can MySQL cope with 5,000 databases? Would performance suck?
- Changes to the schema can be hard to replicate out across all the databases. We would really really have to have an automated plan for this, such as versioning the schema and a script that understands how to take a database from one version to another.
- Doing anything that is common to all our customers might be awkward or impossible
- Similar to above, but any analytics we want to perform across all our customers might be impossible. How should we track usage across all customers for example?