mardi 2 décembre 2014

Comma List vs Multiple Records


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?





Aucun commentaire:

Enregistrer un commentaire