1

I am developing an app where four types of user share same module. So is it a good idea to create:

  1. one table and user_type and user_id (something like that)
  2. table prefixes basically 4 diff tables of same columns
  3. your own suggestion

(keeping in mind its a CakePHP App so joins and contain will be there)

Improve this question
1
  • I'd suggest grouping all common columns in the different user tables into the user_type table then use polymorphic associations to link them together
    – Maru
    Commented Oct 21, 2013 at 9:07

1 Answer 1

Reset to default
2

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.

Improve this answer
2
  • but here amount of data will be increased by 4x times. this is that table which will have lots of data.
    – Mubasshir Pawle
    Commented Oct 22, 2013 at 6:38
  • @MubasshirPawle - You need to weigh the cost of your development time against database space cost. It's exceptionally premature in your design to worry about performance differences. The approach I suggested is a fairly common approach, if that helps allay performance concerns. And even with 1M users at 20 extra bytes of data each, that's only a 20MB addition to your DB. For a 1 million user DB, I would suspect that's a negligible amount.
    – user53019
    Commented Oct 22, 2013 at 10:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.