Update many-to-many relationships

Question

I have two tables, Cars and Drivers, which are connected by a third table, CarDrivers, for many to many.

My user interface allows the user to check any number of drivers related to the current vehicle. Each check indicates that a row should be entered in the CarDrivers table.

My question is: what is the most efficient way to update these lines when the user submits the form?

I need to go through and add a line to CarDrivers for each item that has been marked, and delete one for each item that has been unchecked, leaving those that haven't changed.

The only way I can see is to go through each combination one at a time and add those that don't exist yet, or delete the ones that need to be removed. Is there a smoother way?

I can use Entity Frameworks 4, ADO.NET, direct SQL queries or stored procedures.

Answer 1

This is how the MERGE script was invented for processing, especially in the WHEN NOT MATCHED BY SOURCE .

In general terms, put the current state values ​​in a staging table, then use MERGE to process the INSERT and DELETE actions in one hit.

Here is a brief sketch:

 CREATE TABLE Cars (VIN INTEGER NOT NULL UNIQUE); CREATE TABLE Drivers (driver_licence_number INTEGER NOT NULL UNIQUE); CREATE TABLE CarDrivers ( VIN INTEGER NOT NULL REFERENCES Cars (VIN), driver_licence_number INTEGER NOT NULL REFERENCES Drivers (driver_licence_number) ); INSERT INTO Cars VALUES (1), (2), (3); INSERT INTO Drivers VALUES (22), (55), (99); INSERT INTO CarDrivers VALUES (1, 22), (1, 55); CREATE TABLE CarDrivers_staging ( VIN INTEGER NOT NULL REFERENCES Cars (VIN), driver_licence_number INTEGER NOT NULL REFERENCES Drivers (driver_licence_number) ); INSERT INTO CarDrivers_staging VALUES (1, 55), -- remains (1, 99); -- insert -- DELETE (1, 22) MERGE INTO CarDrivers USING CarDrivers_staging S ON S.VIN = CarDrivers.VIN AND S.driver_licence_number = CarDrivers.driver_licence_number WHEN NOT MATCHED THEN INSERT (VIN, driver_licence_number) VALUES (VIN, driver_licence_number) WHEN NOT MATCHED BY SOURCE THEN DELETE; 

Answer 2

I doubt this is more efficient, but a "slicker" maybe, especially if you don't have a lot of data. When a user submits a form that updates the relationship between machines and drivers, why not first delete all the relationships in their associated CarDrivers, and then insert only the ones they checked? Alternatively, you might have a uniq limit on both columns in CarDrivers, and then just worry about inserting and deleting, rather than checking existing entries in your code.