MySQL one database versus multiple databases

When designing an application – should you put each customer in a separate database or keep them all in one large central database?

When it came to start work on our new Clear Books accounting software last year, we had to make a fundamental database design choice between using a single database for all clients, or having a separate database for each one.

Since there was going to be a fairly large amount of data for each client, we decided to go for the latter, and one year on – I am very happy with our decision.

Advantages

• Easier to balance load. Since each client is in a separate database, it is relatively easy to move them between database servers.
• Faster. One client’s tables being locked won’t affect another clients. Indexes are smaller. If MySQL has to do a row scan (let’s hope it doesn’t!), it’ll be faster.
• MyISAM rsync backups will be faster.
• Easier to clone a clients database for testing purposes (just simply copy the whole db, no need to write a special script to extract their data).

Disadvantages

• Upgrading the database is more complicated, as you have to add new fields to each client database, rather than just once globally. If you are successful you could be dealing with thousands of databases, and so an upgrade script is definitely needed. However, this is also a blessing in disguise as it allows you to test upgrades on a small set of databases before rolling them out generally.
• More complicated to implement in general.

I think the ultimate decision would come down to how much data your store for each client. If it’s a fair amount, then I think separate databases is worth the extra development effort.