I am developing an app where four types of user share same module. So is it a good idea to create:
- one table and user_type and user_id (something like that)
- table prefixes basically 4 diff tables of same columns
- your own suggestion
(keeping in mind its a CakePHP App so joins and contain will be there)
1
Your first option is likely the better of the two.
As points of considerations against the second option:
-
What happens when you get a 5th user type? Do you now need to create a 5th table? What about a 6th or 7th type? what happens when you need to create a hybrid type that is based upon one but has some of the permissions of another?
-
What happens when you need to perform maintenance on the tables? You have quadrupled your work (and opportunity for error!) by having 4 tables instead of 1.
-
Think through the logic of how a user would access the data. Essentially, you’ll have 4 separate pathways through your code because they access their own specific table. That’s 4 times the amount of work and debugging you now need to do at your php layer.
-
How does the 2nd solution scale when there’s more than one table that needs to be access controlled? All of the above considerations multiply by the number of tables affected.
Back to the first option. Generally, group based permissions are the easiest to implement and most scalable for this scenario.
Users will belong to groups (types, in the language of your question), and the groups will have permissions to perform the CRUD actions against the table(s).
I would advise against having user based permissions as the maintenance costs are higher. The difference is this:
- Group based: user belongs to group and the group has permissions
- User based: user has permissions.
If you keep the permissions in another table, it adds another join against your queries in order to make sure the user has the correct authorization.
On the other hand, if you hard-code the permissions into the queries at the php layer then you have a tighter coupling between your DB and your code as well as potentially additional code paths.
Either way, by using the first option you’ll still have less code and chances for errors to deal with than you would in the second option.
2