Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

2 Answers 2

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.

share|improve this answer
    
Thank Mr David Spillett, this is a big help for me. –  Xian Garcia Sep 18 '13 at 0:52

I guess by "context_id" means you indicating TenantID. so that your API will retrive data belongs to only that TenantID. if So; and if the sharing between multiple Tenants is kind o"Sharing ALL" OR "Sharing nothing" then you can also think about creating entity "TenantGroup" where you group Tenants who are sharing their Data to each other. if you are planning on more granular sharing where Tenants can choose what to share and later can update sharing privileges that is the whole "Role based sharing implementation" and the information you gave is not enough to even comment on that.

share|improve this answer

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.