[ACCEPTED]-Updating in a many-to-many relationship-many-to-many

Accepted answer
Score: 11

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

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

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

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

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

Score: 1

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

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

Then you can delete / extract that single 1 tuple.

More Related questions