Application design single vs multiple databases

I was reading the answers for some of the similar questions but it doesn't quite help me understand if I made the right decision.

I have a web application for managing sales. I decided that every company should have their own database.

1. They need to backup their own data (this is easy to do if they have their own database)
2. It is pretty easy to scale if I grow. As I can move databases to different servers.

Wha开发者_如何学编程t are some pros + cons of having multiple databases vs a single?

If the data is self contained within in each database then I think your approach is sound.

The only con I can think of is that if you roll out a schema change to one/more of the tables then you're going to have to apply it to each and every customer's database. This could be an advantage if you want to update some and not others.

The pro that you mentioned about it being easy to scale by splitting customers between MySQL instances is a big pro. I designed a similar application a while ago which uses exactly the same approach.

Are the companies completely isolated from each other? What I mean, is it possible that you may want to query data that covers more than one company. Say a sales report for the year ordered by company? In that case a single database will be more suitable.

Also, as you add more companies are you going to have to add a new database, create tables, then have your application programs be modified to make use of these different databases? That sounds like a maintenence nightmare.

Scaling with MySQL should not be a problem as there are various replication tools that are available.

Backing up a single company should not be a problem either with a little code.

Say you have 10,000 clients and you need to change the structure of table Customers, how are you going to change all the 10K databases?