I am designing a web application SaaS for small construction companies to store certain industry specific data and was researching SaaS best practices, and have come to realize that data isolation is extremely important.
Since I don’t want to offer separate databases for complete isolation, but want to offer each user their own data tables in the DB.
So, here is what I am planning:
Shared Tables - Users, User-Meta (User Options), Auth RBAC …, Groups, Group Meta (Group Options/Settings)
Dedicated Tables - … pre-fixed "GRP_{#}_"
Template Tables - …, pre-fixed "TPL_"
I can use the Yii::$app->db->command to copy the template tables using the MySQL syntax CREATE {new_table} LIKE {template_table} so each group will have their own data repository.
A couple reasons for this design is that (1) by using dedicated data tables, they should stay relatively small and which should have fairly quick response times, and (2) if I forget the WHERE clause in the model “find” methods/queries, other site users’ data won’t be exposed to non-authorized users.
So,
-
Is this a responsible design? If not, what is recommended so that data isolation is achieved?
-
Since the models won’t change as the tables are the same, I think if I just manipulate the model->tableName function to include the proper group pre-fix, all would work just fine. But since the tableName is a Static Function, how can I change this dynamically once the user has logged in?
Thank you for your time!