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.