0

Currently I am trying to setup a relationship between user groups and roles.

I am trying to satisfy:

  1. Multiple roles apply to single group
  2. multiple groups can have same role
  3. multiple groups with single role.

I have a Group Table with RoleID and GroupName and a Role table with RoleID and RoleName. I am able to satisfy 1 and 2 fine but I am stuck on 3. Say currently I have Group 1 mapped to Roles 1&2. Group 2 mapped to Roles 2. Now lets say I have Role 3 which is ONLY available if I have Group 1 and 2 together.

I dont think my current design can handle checking if a Role is only available if the user belongs to 2 groups vs 1. Is there some SQL I can do or some way to construct the tables?

Ex:

Group Table: Group1:RoleID1 Group2:RoleID2 Group3:RoleID3 HOW TO REPRESENT GROUP1+2 MAP TO RoleID3?

Role Table:

RoleID1:Group1 RoleID2:Group2 RoleID3: Group3 RoleID3:NEED GROUPS 1 AND 2 TOGETHER

For my example above, i can have a Group that maps directly to a Role OR 2 Groups maps to the same Role. So Role3 can be available for a User with Group3 OR Groups1+2. I am not sure how to differentiate the 2. Will i need to redesign the datatables or have update to my SQL?

New contributor
shaselai is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
  • Please explain what you mean by "apply", "have", "mapped". Please also explain in more detail what the relationship between groups and roles can be. Your three points are not clear. – Lutz Horn May 16 at 12:24
  • Hi. So here's the situation. I have a list of users who can belong to multiple User Groups. Each User Group can have one or more Roles, and each Role is mapped to a Permission table (like "Writer" Role can have Permission of 2,3,4 etc.). For example, right now I made it that UserGroup have a format of RoleID:UserGroupName. and in Role table i have RoleID:RoleName. This doesn't work if a situation of UserGroup1+UserGroup2 = Role3 since i am doing 1 to 1 map. I am not sure if there's some query magic i can do or a way to design the tables that way? Thanks – shaselai May 16 at 13:13
  • Please edit your question. – Lutz Horn May 16 at 13:17

Your Answer

shaselai is a new contributor. Be nice, and check out our Code of Conduct.

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

Browse other questions tagged or ask your own question.