Multitenancy data architecture

Currently working on booking management system. This is a multitenant application and there will be around 50 tenants.

We are planning to host this ASP.Net MVC4/SQL Server 2008 application in some hosting provider like winasp.net, etc(Yet to decide)

Business Model Diagram

There are many levels of users like Super Admin, Tenant Admin, Customer Service, Doctors are described in the above pics.

For achieving this as a Database model, we chosen Shared Database with Shared Schema approach mentioned in MSDN Multitenant Data Architecture

Mean we added a column TenantId in each table

Our shared database & shared schema decision was made based on the below

  1. No of tenants (50 +)

  2. Easy to share the common meta data between the tenants

  3. Moving big tenant(one/two) into a seperate instance if a tenant have more volume of data

We are now in progress and we still afraid of below issues to address

  1. Data Security -> Everytime need to pass/check TenantId

  2. Backup for a single tenant --> Need to write a SQL query for backup and Considering foreign key/auto increment is headache at backup

  3. Data volume. Single database stores all tenant data, Querying data is slow

  4. Indexing (Not sure whether we need to index all TenantId column in each table, since it involves in all WHERE

There are other options like

  • Single database/tenant
  • Shared database, seperate Schema

Also This Article has added some more approaches

We would like to get some advise/better design for our current design.

  • New approach match the above business diagram

  • A tenant admin/customer service user must be able to see the sub tenant records

  • Query performamce

  • Common Meta data sharing between tenant

  • Tenant Specific Meta data

  • Tenant Specific Data Fields (optional)

  • Easy backup

Answers


Seems to me that you should revisit your decision of having a shared database. If you have a requirement of strict data separation because of the confidentiality than you should have separate databases.

Indexing (Not sure whether we need to index all TenantId column in each table, since it involves in all WHERE

Yes, you will have to index TenantId in each table and include it in all the queries.

Also, it looks like you've made a decision of using SQL Server before you analysed the requirements. There are probably more natural solutions for storing multitenant data, ie. RavenDB, that will make sharding / backups much simpler. I don't want to start any discussion about nosql, etc. - just suggesting that one should start with the requirements and choose the appropriate technology later.


Need Your Help

Prototype Constructor Call

javascript

Instead of using the call method, would it be applicable to utilize the following, e.g:

Bringing mainwindow to front after splash-screen shuts down

wpf splash-screen

I have run into a problem with a custom wpf splash-screen implementation.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.