Unanswered: Design Issue: How to design the data model for SAAS based application?
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:
COMPANY_ID | COMPANY_NAME
100 | Acme Inc.
101 | E Technologies.
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.
1. 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=<blank>
2. 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.
3. 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.?