Database for Saas app, one or multiple db's?

Databases
1

A quick question.

If you’re building a web application that will run as Software as a Service, would you have one big database to manage all instances of your application, or would you create a script that creates a new database for each account?

So, in the case of the second scenario, the app would have one database that holds a list of accounts, and the name of their database, login details etc. Then each account would then have it’s own database which holds all of the app data for that account?

I can see the advantage of having one large database, but I feel it’ll become difficult to manage, and there’ll need to be extra security it place to make sure accounts can’t see other accounts data.

Then on the other hand, if you have a database for each account, then if you make a change or update to one database you have to make it to them all.

How have others done it?

Thanks

2

In a vaccum, a centralized accounts database with individual databases per account is probably the best way to go. Same automated deployment system that brought you the database should be able to migrate it when appropriate. Big advantage to having separate databases is you don’t have to upgrade all the clients at once, you can break that up.

That is all in a vaccum though – decision should be made knowing the nature of the beast . . .

3

I recently went through this decision process. Some factors that I weighed were the size of each tenant’s data, their sensitivities regarding database sharing, and the amount of custom fields each tenant would require. You may have already read it, but this is my favorite article on the subject. Worth reading in-depth. http://msdn.microsoft.com/en-us/library/aa479086.aspx