Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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)

Edit: here's the highlights from my research from other sources:

Should I use one database per application or share a single database amongst multiple applications

Splitting up a single project into libraries

Supporting multitenancy

Multi-tenancy - single database vs multiple database

http://msdn.microsoft.com/en-us/library/aa479086.aspx

http://stackoverflow.com/questions/2213006/how-to-create-a-multi-tenant-database-with-shared-table-structures

http://cloudcomputing.sys-con.com/node/1610582

http://blogs.msdn.com/b/cbiyikoglu/archive/2011/03/23/moving-to-multi-tenant-model-made-easy-with-sql-azure-federations.aspx

http://ask.sqlservercentral.com/questions/3615/one-database-or-multiple.html

http://devlicio.us/blogs/anne_epstein/archive/2009/04/24/the-case-for-multiple-dbs-in-multi-tenancy-situations.aspx

http://ayende.com/blog/3497/multi-tenancy-the-physical-data-model

http://www.codeproject.com/Articles/51334/Multi-Tenants-Database-Architecture

http://discuss.joelonsoftware.com/default.asp?design.4.319460.16

http://stackoverflow.com/questions/3479297/multiple-application-using-one-database

http://stackoverflow.com/questions/1676552/single-or-multiple-databases

http://mikehadlow.blogspot.co.uk/2008/11/multi-tenancy-part-1-strategy.html

http://mikehadlow.blogspot.co.uk/2008/11/multi-tenancy-part-2-components-and.html

http://www.sqlservercentral.com/Forums/Topic893107-373-1.aspx#bm1047297

share|improve this question
    
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 Mar 23 '12 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 Mar 24 '12 at 4:11

3 Answers 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 )

share|improve this answer
    
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 Mar 24 '12 at 10:31

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.

share|improve this answer
    
+1 This will also allow you to avoid having to configure MSDTC and take on the associated overhead (two phase commits) –  brian Mar 23 '12 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 Mar 23 '12 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 Mar 24 '12 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 Mar 24 '12 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? –  Yash Oct 18 '13 at 12:36

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.

share|improve this answer
    
What would your suggestion be as to how to manage the common functionality with minimal pain? –  RichardW1001 Mar 24 '12 at 10:29
    
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 –  ssmusoke Mar 24 '12 at 10:44

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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