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)
Best Answer
Here's the highlights from research from other sources (originally from revision 2 of the question):
Should I use one database per application or share a single database amongst multiple applications
Supporting multitenancy
MSDN Multi-Tenant Data Architecture
SO How to create a multi-tenant database with shared table structures?
CloudExpo SaaS - Multi-Tenant Database Design Options
MSDN blogs Moving to Multi-Tenant Database Model Made Easy with SQL Azure Federations
Ask SQL Server Central One database or multiple?
SQL Server Central Mixing tables from different databases/applications into one database
Ayende@Rahien blog Multi Tenancy - The Physical Data Model
Code Project Multi Tenants Database Architecture
Joel on Software Multi-tenant database design
SO Multiple application using one database?
SO Single or multiple databases
Code Rant - Multi-tenancy Part 1, Strategy and Part 2, Components and Context