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?
我应该首先删除所有用户角色，然后添加所选用户角色吗？或者进行某种匹配？
2 个解决方案
#1
6
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.
1.删除所有角色并重新插入这是一种直截了当的方法。删除用户的所有角色，然后重新插入。通常，用户只属于少数角色（少于10个）。此外，很有可能没有其他外键链接到这个多对多表。
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.
2.跟踪更改并仅应用更改这是更多的工作，但更有效，即使在这种情况下稍微。像ORM这样的工具可以轻松跟踪和应用这些类型的更改。
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.
3.在用户进行更改时应用更改在这种情况下，我假设应用数据库更改是可接受的，因为最终用户将用户与角色关联。也许它是一个本地数据库，每个事务都是短暂的。但我想这是不太可能的情况。
I don't think there is anything wrong for this particular case to delete and re-insert.
我不认为删除和重新插入此特定情况有任何错误。
#2
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?
如果一个人删除了一个角色，为什么不传递userID和roleID并删除那条记录呢？为什么要删除特定用户ID的所有roleID，然后再次读取它们？
From my comment above, pass two params: UserID and RoleID
从我上面的评论中，传递两个参数：UserID和RoleID
Then you can delete / extract that single tuple.
然后你可以删除/提取那个单元组。
#1
6
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.
1.删除所有角色并重新插入这是一种直截了当的方法。删除用户的所有角色，然后重新插入。通常，用户只属于少数角色（少于10个）。此外，很有可能没有其他外键链接到这个多对多表。
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.
2.跟踪更改并仅应用更改这是更多的工作，但更有效，即使在这种情况下稍微。像ORM这样的工具可以轻松跟踪和应用这些类型的更改。
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.
3.在用户进行更改时应用更改在这种情况下，我假设应用数据库更改是可接受的，因为最终用户将用户与角色关联。也许它是一个本地数据库，每个事务都是短暂的。但我想这是不太可能的情况。
I don't think there is anything wrong for this particular case to delete and re-insert.
我不认为删除和重新插入此特定情况有任何错误。
#2
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?
如果一个人删除了一个角色，为什么不传递userID和roleID并删除那条记录呢？为什么要删除特定用户ID的所有roleID，然后再次读取它们？
From my comment above, pass two params: UserID and RoleID
从我上面的评论中，传递两个参数：UserID和RoleID
Then you can delete / extract that single tuple.
然后你可以删除/提取那个单元组。