0

I am currently building out an application that will contain many users, two roles and two groups. Logically we want users to be able to be administrators of other users within the two groups.

User Table


UserID         varchar    PK
DisplayName
Email
Role           int        FK `role`.`RoleID`

DATA [jschmo,Joe Schmo,joe@schmo.com,1]

Role Table


RoleID         int        PK     AI
RoleName       varchar

DATA [1=Master,2=Admin,3=User]

Group Table


GroupID        int        PK     AI
GroupName

DATA [1=Marketing,2=Infrastructure]

A user could potentially be an admin of both groups. I don't see the option to multi-select foreign keys but as I understand it, having a comma-delimited list in a column is a bad idea and I should use multiple records but I don't like the idea of having multiple records for each user.

Q: How would I keep track of which groups a user is an admin of?

  • 1
    Sounds like a question that could be solved by applying Normalization rules. – Joishi Bodio Dec 2 '14 at 19:36
7

Please, don't store comma-separated lists in a single column. This is just a disaster waiting to happen. If these are separate facts, they should be stored separately.

Table GroupMemberRoles
GroupID  FK
UserID   FK
RoleID   FK
(PK on all three, with perhaps other constraints)

Your queries (say, to find the admins of a certain group) should be of the form:

WHERE RoleID = 1 AND GroupID = @GroupID;

And NOT:

WHERE RoleID = 1 AND ',' + GroupIDList + ',' LIKE ',' + RTRIM(@GroupID) + ','; 

Or to find which groups a user is an admin of should be similarly simple:

WHERE RoleID = 1 AND UserID = @UserID;
  • 1
    As I said - normalization. ;) – Joishi Bodio Dec 2 '14 at 19:47
  • @JoishiBodio Yes, but one-word answers are not very useful. :-) – Aaron Bertrand Dec 2 '14 at 19:49
  • 1
    Which would be why I put it as a comment. ;) (with hopes that he'd go do some googling and reading) – Joishi Bodio Dec 2 '14 at 19:50
  • I ended up with a GroupAdmins table with the exact columns you specified here. Thank you both @JoishiBodio and Aaron Bertrand. – Nicholas Anderson Dec 2 '14 at 20:03
  • 2
    @NicholasLAnderson Great, but I really hope you're not going to also have a GroupMasters table and a GroupUsers table (and then add another table when you dream up another role). The whole point of the RoleID column was to not make the data all about a specific role. – Aaron Bertrand Dec 2 '14 at 20:15

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.