29

We have a number of clients, whose systems share some functionality, but also have quite a degree of diversity. The number of clients is growing - always a healthy thing! - and the diversity between their businesses is also increasing.

At present there is a single ASP.Net (Web Forms) Web Site (as opposed to web project), which has sub-folders for each tenant, with that tenant's non-standard pages. There is a separate model project, which deals with database access and business logic.

Which is preferable - and most importantly, why - between having (a) 1 database per client, with only the features associated with that client; or (b) a single database shared by all clients, where only a subset of tables are used by any one client.

The main concerns within the business are over:

  • maintenance of multiple assets - backups, version control and the like
  • promoting re-use as much as possible

How would you ensure these concerns are addressed, which solution is preferable, and why? (I have been also compiling responses to similar questions)

Improve this question
3
  • Is there any chance this will move to a PaaS cloud environment like Azure? If so, you'll want to consider best practices for the environment as well. Last time I looked, MS recommended multiple databases for multitenant software on Azure.
    – Harper Shelby
    Commented Mar 23, 2012 at 21:53
  • Thanks for asking. I've been wondering similar things, but haven't been able to put into question format as eloquently as you.
    – MathAttack
    Commented Mar 24, 2012 at 4:11
  • You should read the multi-tenancy blog series of Ayende. He makes some very good points about multi vs single database.
    – Sebazzz
    Commented Apr 15, 2015 at 15:00

5 Answers 5

Reset to default
11

If you are using SQL Server, use one database but use schemas. Use dbo for stuff that is general to all clients and create a schema for each client and make that the default schema for users from that client. Now you can have a general object (say a getBudget proc) in the dbo schema and a customized one for the client in their schema with the same name.

Improve this answer
5
  • +1 This will also allow you to avoid having to configure MSDTC and take on the associated overhead (two phase commits)
    – brian
    Commented Mar 23, 2012 at 21:38
  • And hopefully you avoid the trap of having columns like CustomField1 through CustomField30 and/or having tables like Budget, BudgetEx, BudgetCustom, BudgetCustomerName, BudgetAnotherCustomerName, etc.
    – jfrankcarr
    Commented Mar 23, 2012 at 21:52
  • There's an existing data access layer that uses a lot of stored procedures - 190 tables, 5 code-generated procedures per table, plus some custom ones - while the medium term goal is to introduce Entity Framework, would there be a need to replicate the stored procedures for each schema?
    – RichardW1001
    Commented Mar 24, 2012 at 10:36
  • @RichardW1001: depends. you can do dynamic SQL in the sp, in order to make it generic, but that of course depends upon them having at least somewhat similar structure. A possible alternative for dynamic sql, would be Synonyms, unfortunately, as I understand them, they are system wide and not contained within a transaction, so not suitable for concurrent usage with different values.
    – jmoreno
    Commented Mar 24, 2012 at 17:46
  • If we use multiple schemas, using EF Code First will it be possible to migrate all the schemas to the latest version after the system goes live?
    – Yashvit
    Commented Oct 18, 2013 at 12:36
4

Since the clients databases and functionality are diverging, then it means that at one point they will end up being different systems, so in this case I would recommend separate systems since the costs of maintaining the customizations for each client will outweigh the benefits of a single database system.

Single database systems are best for when the changes between different customers are merely configurations but not additional features for each client.

Improve this answer
2
  • What would your suggestion be as to how to manage the common functionality with minimal pain?
    – RichardW1001
    Commented Mar 24, 2012 at 10:29
  • 1
    In your version control system, maintain a head for the core application, and create a main branch for each customer, with sub-branches for new features they need to maintain. Develop customer specific features in the branches, with options to update the trunk etc. You can then spinup customer specific environments with ease whenever you need them
    – Stephen Senkomago Musoke
    Commented Mar 24, 2012 at 10:44
3

You're missing some concerns. Problems will come with growth. If you can assume that someday you'll grow bigger than one DB server - one complex database will definitely cause you a headache. Unless you'll invest in architecture in advance. But it is also expensive step )

So, just do not forget, that it is both many times cheaper and many times easier to scale out few different databases, than huge ones )

Improve this answer
1
  • Do you have any data that confirms the ease of scaling? If so, it would make a very compelling case. Could you elaborate on the other missing concerns? I'm trying to build as complete an argument on both sides as possible.
    – RichardW1001
    Commented Mar 24, 2012 at 10:31
1

One area that I haven't seen addressed in the answers is the problems with correctly securing a multi-tenant DB application. Multi-tenant apps have to be very carefully designed to avoid security issues. Most Databases and the apps provide some, but not complete isolation - there are usually ways to directly or indirectly infer data across the DB schemas. And quite a few shared resources (logs and other files, DBA tables, cursors...) that can, at the very least, lead to easy Denial of Service attacks on other tenants, and usually much more.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.