12

I have a many-to-many table that stores a record for each allowed role a user can have. If a user updates his roles (add, and removes) roles how should I handle this?

Should I delete all the users roles first and then add the selected ones? Or do some sort of matching?

chobo
  • 31,561
  • 38
  • 123
  • 191
  • thats why we have parameters, you pass the userID and the roleID as a parameter and then you do: `DELETE FROM MyTable WHERE userID = @UserID AND roleID = @RoleID` – JonH Feb 17 '12 at 20:16
  • 5
    This is actually a better question than it seems on the surface. If you have a UI that lets you edit the many side of the table and updates it in batch mode when the user hits save (instead of doing them one at a time) it is possible another user could modify the table underneath you so you aren't sure if, for example, another user added roles while the current user was editing in the UI – JohnFx Feb 17 '12 at 20:21

2 Answers2

13

There are many ways to skin this cat, a few techniques I can think of:

1. Delete all roles and re-insert
This is a straight-forward approach. Remove all the roles for the user and just re-insert. Normally the user only belong to a few roles (less than 10). Also, there is a good chance that no other foreign-keys link to this many-to-many table.

2. Keep track of the changes and apply only the changes
This is more work but more efficient, even if just slightly in this case. Tools like ORMs makes tracking and applying these type of changes a breeze.

3. Apply the changes as the user makes the change
In this case I assume that it is acceptable to apply the DB changes as the end-user associates the user to roles. Perhaps it is a local database and each transaction is short-lived. But I guess this is a unlikely scenario.

I don't think there is anything wrong for this particular case to delete and re-insert.

Philip Fourie
  • 111,587
  • 10
  • 63
  • 83
1

If a person removes a role why not pass the userID and roleID and remove that one record? Why would you want to delete all roleID's for a specific userID and then readd them again?

From my comment above, pass two params: UserID and RoleID

Then you can delete / extract that single tuple.

JonH
  • 32,732
  • 12
  • 87
  • 145
  • The way the ui is setup the roles are set via check boxes where you can check or uncheck multiple – chobo Feb 17 '12 at 20:27
  • @chobo - so send multiple parameters, you can use `IN` etc. Either way if there arent too many roles you can simply remove all and readd only checked ones. – JonH Feb 17 '12 at 20:29