Editing records in association table in many-to-many relationship

This is something that has bothered me for a while, and I finally want to get an answer to it.

Say I have Employees and Departments. There's a many-to-many relationship between the two, with an association table named EmployeeDepartments.

Employees
-------------------
EmployeeID (PK)

Departments
-------------------
DepartmentID (PK)

EmployeeDepartments
-------------------
EmployeeID (FK)
DepartmentID (FK)

I have a page where a user can edit an employee. There is a list of checkboxes that shows all the departments, and the departments that the employee belongs to will be checked. The user can开发者_运维技巧 then check/uncheck which departments the employee belongs to.

When I edit an employee and go to save the data, how should I handle those departments?

The way I've been doing it is to delete every record in the EmployeeDepartments table for that employee. And then for each department that the user selected, I add it to EmployeeDepartments.

It works, but there has to be a more efficient way to do this, it just seems wrong. If an employee is in 5 departments, and I go to add that employee to another department, I will have to delete 5 records from EmployeeDepartments, and then add 6 records.

Just process the change delta. That is, look at the currently "selected" items, then the items to select, and then either add or delete appropriate. This can be computed using the set operations (which are trivial in LINQ).

1. Delete items in old - new (set difference, or Except)
2. Add items in new - old (set difference, or Except)
3. (Items in new ^ old should be unaffected)

Much of this work can be handed off to the database, if desired. (Delete except where... and insert if not exists... for instance. Also see the Standard SQL MERGE statement and non-Standard equivalents and extensions).

Make sure to correctly use database transactions to ensure consistency.

Happy coding.

If your SQL product supports it, you can use Standard SQL's MERGE. If your SQL product is SQL Server then you are in luck: its MERGE has a nifty extension IF NOT MATCHED BY SOURCE which allows you to DELETE in addition to INSERT and UPDATE.

Here's a simple example (e.g. omitting referential integrity constraints):

CREATE TABLE EmployeeDepartments 
(
 EmployeeID INTEGER NOT NULL, 
 DepartmentID INTEGER NOT NULL, 
 UNIQUE (DepartmentID, EmployeeID)
);

INSERT INTO EmployeeDepartments (EmployeeID, DepartmentID)
   VALUES (1, 1), 
          (1, 2);

Say after editing you place the values in a staging table:

CREATE TABLE StagingTable 
(
 EmployeeID INTEGER NOT NULL, 
 DepartmentID INTEGER NOT NULL, 
 UNIQUE (DepartmentID, EmployeeID)
);

INSERT INTO StagingTable (EmployeeID, DepartmentID)
   VALUES (1, 1), 
          (1, 3);

In plain English, row {1, 3} will be inserted, row {1, 2} will be deleted and row {1, 1} will remain:

MERGE INTO EmployeeDepartments
   USING StagingTable AS S1 
      ON EmployeeDepartments.EmployeeID = S1.EmployeeID
         AND EmployeeDepartments.DepartmentID = S1.DepartmentID
WHEN NOT MATCHED THEN
   INSERT (EmployeeID, DepartmentID)
      VALUES (EmployeeID, DepartmentID)
WHEN NOT MATCHED BY SOURCE THEN
   DELETE;