Updating in a many-to-many relationship

Question

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?

Answer 1

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.

Answer 2

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.