Multi-Tenant (SaaS) Database Schema with Sharing

schema

Guys I just need your opinion which is the best approach to design a schema of a database for a SaaS application. I want it all on a single database,

At first I think I can simply put a context_id to all the tables and table values. This context_id is the id of the SaaS subscriber. And all my queries will be including a where clause to the context_id.

However I also want it flexible which I can provide sharing between subscribers. Say my application is a clinic management system. One practitioner is subscribed then he/she is actually collaborating to another subscriber in which they are sharing their records.

So bottomline is what can be the best design with the schema? Thank you so much

Best Answer

Often in a multi-tenant system there is little or no sharing (in fact it is often desirable that other clients can be completely unaware of the others' existence) in which case the simple context_id is sufficient. It is generally sufficient for a hierarchical tenant structure (companies within groups, resellers, etc.) too.

For simple sharing this might work:

ThingTable                                   AccessControl
============                                 =================
ThingID (PK)  <----------------------------  ThingID  (PK, FK)
Owner   (FK)  --.                       .--  TenantID (PK, FK)
Property 1      |     Tenants           |    AccessLevel
Property 2      |     =============     |
  ...           `-->  TenantID (PK)  <--'
Property N            Name
                      etc...

Where AccessLevel could be a simple boolean representing read-only/read+write, or a more complicated collection of properties. You could even remove the "Owner" column from your tables unless you want it as a convenience and include ownership as part of the data in the access control table, or if you have no concept of an owner the objects you are modelling you can do away with it completely (it is usual for the "owner" of an object to have greater control, such as being the only user/group able to chance the access control for other users/groups, but this can be implemented in your access control table without needing a specific owner column).

If you are tracking multiple types of Thing this way, you will need to abstract out the concept so that you have a single entity to point the foreign key in AccessControl at, like so:

ThingTable1                                          AccessControl                           
================                                     =================                       
ThingID (PK, FK)  --.                           .--  ThingID  (PK, FK)                       
Property 1          |                           |    TenantID (PK, FK)  --.                  
Property N          |                           |    AccessLevel          |                  
                    |                           |                         |                  
                    |                           |                         |                  
ThingTable2         |     Things                |                         |                  
================    |     =================     |                         |     Tenants      
ThingID (PK, FK)  --+-->  ThingID      (PK)  <--'                         |     =============
Property 1          |     Owner        (FK)  -----------------------------+-->  TenantID (PK)
Property N          |     Shared Property 1                                     Name         
                    |                                                           etc...       
                    |                                                                        
ThingTable3         |                                                                        
================    |                                                                        
ThingID (PK, FK)  --'                                                                        
Property 1                                                                                   
Property N

Any properties common to all the "Thing" tables would go in the general "Thing" table along with the owner (if yuo are are noting the owner that way, of course).

You can get much more flexible than this: allowing for groups of tenants (from simple groups as used in Unix-a-like filesystems through to supporting hierarchical relationships), having control of which tenants or groups can see each other, ans so forth - but from your description I'm assuming such extra features would be overkill for your current needs so there would be little point complicating the design to include them.

Related Question