Many-to-Many Update

Question

I have a many-to-many table that stores a record for each permitted role that a user can have. If a user updates roles (adds and removes) roles, how do I do this?

Should I remove all user roles first and then add the selected ones? Or do some sort of mapping?

Answer 1

There are many ways to trick this cat, several methods that I can think of:

1. Removing all roles and reinstalling
This is a direct approach. Remove all roles for the user and simply reinsert. Typically, the user applies to only a few roles (less than 10). In addition, there is a good chance that no other foreign keys will contact this many-to-many table.

2. Keep track of changes and apply only changes
This works more, but more efficiently, even if a little in this case. Tools such as ORMs allow you to track and apply these types of changes.

3. Apply changes when user made changes
In this case, I assume that it is acceptable to apply database changes since the end user associates the user with roles. Perhaps this is a local database, and each transaction is short-lived. But I guess this is an unlikely scenario.

I do not think that in this particular case there is something wrong for uninstalling and reinstalling.

Answer 2

If a person deletes a role, why not pass userID and roleID and delete one entry? Why do you want to remove all roleIDs for a specific userID and then read them again?

From my comment above we pass two parameters: userID and roleID

Then you can remove / extract this single tuple.