Design Issue: How to design the data model for SAAS based application?

Question

Designing data model for STANDARD and USER SPECIFIC records for SAAS based model.

In my SAAS based application, I have users and their associated (One to One) roles. Tenants can create their own roles specific to their company and assigned to their users. And the SYSTEM have some standard roles provided for the tenants to use. The SYSTEM defined standard roles are common to all tenants.

I have the ROLE and COMPANY tables as follows:

Table: COMPANY

    COMPANY_ID   |  COMPANY_NAME    
    100          |  Acme Inc.
    101          |  E Technologies.

Table: ROLE

    ROLE_ID |   COMPANY_ID  | ROLE_NAME         | IS_STANDARD_ROLE
    1       |               | ADMINISTRATOR     |Yes
    2       |               |MANAGER            |Yes
    3       |   100         |MyAdmin            |No
    4       |   100         |MySpecialist       |No
    5       |   101         |Supervisor         |No

Here I have ROLE.COMPANY_ID references COMPANY.COMPANY_ID

I am trying to figure out the best way to accommodate both standard and user defined roles in the same table and have Hibernate 3.0 with annotations can pull with no complexity.

Here are the alternatives I am having in place.

I can have both standard and customer defined roles in the same table as above and leave the ROLE.COMPANY_ID field blank(if mysql permits) for standard. But the challenge is for hibernate3.0 to pull both ROLE.COMPANY_ID=100 OR ROLE.COMPANY_ID=
I can define a dummy company called SYSTEM in the company table and refer all standard/SYSTEM records to the Company called SYSTEM. Again the same challenge to pull records with OR in hinernate 3.0 with aootations. Not sure, how to do this OR clause on hibernate 3.0 with annotations without custom HQL? Some how , team don’t like the idea of dummy company record in database.
I can create copies of standard records for each tenant and assign them to their own company_id. But the chanllange here is, I will have at least 80 standard records for each tenant and If I expect 1000 free trail tenants, I will end up allocating 80,000 records space. Any thought with this design? Not a clover option, but Choose this with no options left..

Instead I would prefer to have one copy of standard records, where all tenants can share as they are SYSTEM records.

Any thoughts of Mr. Perfect’s design in terms of programmability, maintenance, DB space for SAAS startup.?

NaturalData NaturalData · Accepted Answer · 2014-03-29T14:50:32

I've seen a couple options.

My preferred is to have two tables, one for standard roles and one for customer-defined roles. This is subtle but they are, essentially, two different entities: a role that is common to all tenants and a tenant custom role. It is possible that these will eventually differ in attributes and/or relationships.

The other is to collate them in one table as in your first two suggestions, which are essentially the same solution. The reason I don't like this is that you overload the definition of COMPANY_ID. You will almost always regret overloading a column definition.

Either way, I would select with one of the following methods:

1) Have Hibernate call a stored procedure that unions the two sets.

2) Have two calls and assemble in your collection. At a minimum you would cache the standard roles since they are not volatile. So this would not add a performance hit.

Design Issue: How to design the data model for SAAS based application?

2 Answers