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:
roles_table
* has_record
* record_str
user_roles_table
* 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.
Best Answer
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