Inside a database-centric application that's created for multiple clients, I have always think it is "better" to utilize a single database for those clients - associating records with proper indexes and secrets. In hearing the Stack Overflow podcast, I heard Joel point out that FogBugz uses one database per client (therefore if there have been 1000 clients, there'd be 1000 databases). Do you know the benefits of by using this architecture?

I realize that for many projects, clients need immediate access to all their data - such a credit card applicatoin, it's apparent that every client needs their very own database. However, for projects in which a client need not access the database directly, what are the benefits of using one database per client? It appears that when it comes to versatility, it's easier to utilize a single database having a single copy from the tables. It's simpler to include additional features, it's simpler to produce reviews, and it is just simpler to handle.

I had been pretty positive about the "one database for those clients" method until I heard Joel (a skilled developer) point out that his software utilizes a different approach -- and I am a little wrongly identified as his decision...

I have heard people cite that databases decelerate with a lot of records, but any relational database with a few merit is not likely to have this problem - particularly if proper indexes and secrets are utilized.

Any input is greatly appreciated!

Assume there is no scaling penalty for storing all of the clients in a single database for most of us, and well set up databases/queries, this is fairly true nowadays. If you are not one of these simple people, well, then the advantage of just one database is apparent.

In cases like this, benefits range from encapsulation of every client. In the code perspective, each client is available in isolation - there's no possible situation where a database update might overwrite, corrupt, retrieve or alter data owned by another client. This simplifies the model, as you don't have to ever think about it that records might fit in with another client.

Additionally you get advantages of separability - it's trivial to drag the data connected having a given client ,and move them to another server. Or restore a backup of this client once the contact to express "We have erased some key data!", while using builtin database systems.

You receive simple and easy , free server mobility - should you outscale one database server, you are able to just host new customers on another server. When they were all-in-one database, you'd have to either get beefier hardware, or run the database over multiple machines.

You receive easy versioning - if a person client really wants to remain on software version 1., and the other wants 2., where 1. and a pair of. use different database schemas, there is no problem - you are able to migrate one without needing to pull them from one database.

I'm able to think about a couple of dozen more, I suppose. But overall, the important thing concept is "simplicity". The merchandise handles one client, and therefore one database. There's never any complexity in the "However the database also consists of other clients" problem. They fit the mental type of the consumer, where they exist alone. Advantages like having the ability to doing easy confirming on all clients at the same time, are minimal - how frequently would you like a study overall world, instead of only one client?

Well, let's say your clients informs you to definitely restore for an earlier version of the data because of some botched import job or similar? Imagine the way your clients would feel should you said excitedly "you cannot do this, as your information is shared between all of our clients" or "Sorry, however your changes were lost because client X required a restore from the database".

To make it simple. You can be certain that the client is just seeing their data. The customer with less records does not need to pay the penalty of needing to contend with 100s of 1000's of records that might be within the database although not their own. I do not care how good things are indexed and enhanced you will see queries that determine that they need to scan every record.