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

  • I am unaware as to why you want the solution to be based on centralized database. As multi-tenant design goes, you should keep the links between tenants to minimum. That way you can achieve performance isolation between tenants. If you like to have a shared area, you can create it aside. Also, if you are on cloud and performance > storage in your case, Maybe consider a more elegant solution such as a queue table where you write the subscriptions that has been updated and query directly through the target tenant. Again, depends on the numbers and if you're dealing with web-based endusers. – Johns Oct 18 '15 at 13:59

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

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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