One database per client or all clients in one database?

Question

I have to design a database structure in SQL Server for my project, being a Management Information System.

I have one master database, and whenever a new organization subscribes to our web-based application, my system creates a new copy of the master database for that client. As time goes by the number of clients increases, so the amount of client database also increases.

Is this good a approach I am using, or do I have to use one single database for all clients?

Answer 1

No, creating one database per client, assuming a client is just an entity in your database as opposed to a separate user of a multi-tenant application, is not a good idea.

If you want to register other entities, like hardware, projects or other resources under a client, then you store either in a separate table and create a foreign key from the resource to the client, indicating a "has-a" relation.

That would look like this:

Clients:

Id Name
1  Foo
2  Bar

Resources:

Id ClientId Name 
1  1        Resource of Foo
2  1        Resource of Foo
3  2        Resource of Bar

You could also take the above approach with a multi-tenancy system, where all users of your application run on the same database and application.

With that approach you'll have different concerns though. For example the single database might grow out of proportions, harming performance and ease of backup and restore. You'll have to add checks throughout the application that any database access occurs to rows the current user has permissions to. Rolling back an action of one user may be harder, as well as introducing new features for specific users (since they all run on the same software and database schema).

Read more about the term "multitenancy", which is what you're looking for:

• Wikipedia: Multitenancy
• MSDN: Multi-Tenant Data Architecture
• Multi-tenancy: Individual database per tenant
• ASP.NET Multi tenant application with tenant specific roles

Answering how to implement multitenancy would be too broad, see for example the MSDN blog series about it in the links above. As for the performance question: only you know. It all depends on your code, your data, your schema, your hardware and so on. Benchmark it.