I have a user_roles table which I use to manage permissions and user's access to certain things. For example, a team manager is a role. Since a team manager belongs to a specific team, the way I handle that is by having extra fields in these tables for example:

  * has_record
  * record_str

  * record_id

So if I need to get the team of a team manager, I would have to check the roles record to get the record_str, in this case 'Team' and instantiate an object using that string and the record id in user_roles.

I did it this way because it was a generic way to apply it across all roles in which case I can continue to add roles without changes. Its also nice for adding roles to users. If I am assigning roles, and it has a record, I can then query for the records using the str and display the options in a drop down.

My concern with this is the performance. Do you guys think it will last at a high scale?

The other option I was considering was to just create a table for each user role. This is less modular since if I want to create a role I need add a table, but it provides a more direct way of getting from the user_role to the record without all the joins in between.


I would use your current design over creating a table for each user role. Why create a new role, when your code may have to cycle through several tables to get an answer that could have come from one place. (Opinion: You would just be making more work for yourself.)

Of course, a Team, a TeamManager, and a TeamMember may all have some attributes that are related only to their individual roles.

If you are facing that challenge, then a supertype-subtype design can work well for this issue. All the common data is in the supertype table, but each subtype contains data unique to that role/task/etc. (Whatever you are trying to manage.)

A Microsoft Tutorial give a basic description at Lesson 5: Supertypes and Subtypes at https://msdn.microsoft.com/en-us/library/cc505839.aspx

  • So are you basically suggesting that I keep my current design, and for roles that need more specific attributes I can create a new table? – Long Nguyen Feb 26 '16 at 15:20
  • Yes, the simple design seems flexible enough, but will naturally get augmented as you work out other requirements. Performance can become a problem, but a SQL Server (even the free version) can handle a lot of data quickly. So I would not worry about the number of rows of data unless you have a massive database. Performance will also benefit from good relational design, efficient coding, and the needed indexes. – RLF Feb 26 '16 at 15:29
  • I guess my last question is then, for role checking should I be sticking to one method or can I mix and match? What I mean is, based on my design above traditionally I would have to check roles like so. select user_id from user_roles where role_id = (select id from roles where role = ROLE_NAME); but assuming some roles with have their own tables, it would be easier to do select user_id from ROLE_TABLE_NAME; is there any thing wrong with this approach? – Long Nguyen Feb 26 '16 at 19:26
  • The more you can handle role checking in a single place, the simpler the process will be. There may be some cases that data needs to be obtained from another table, but I would design to minimize the possibility.. – RLF Feb 26 '16 at 19:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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