Efficient way to update SQL 'relationship' table

Question

Say I have three properly normalised tables. One of people, one of qualifications and one mapping people to qualifications:

People:

id | Name
----------
1  | Alice
2  | Bob

Degrees:

id | Name
---------
1  | PhD
2  | MA

People-to-degrees:

person_id | degree_id
---------------------
1         | 2         # Alice has an MA
2         | 1         # Bob has a PhD

So then I have to update this mapping via my web interface. (I made a mistake. Bob has a BA, not a PhD, and Alice just got her B Eng.)

There are four possible states of these one-to-many relationship mappings:

• was true before, should now be false
• was false before, should now be true
• was true before, should remain true
• was false before, should remain false

what I don't want to do is read the values from four checkboxes, then hit the database four times to say "Did Bob have a BA before? Well he does now." "Did Bob have PhD before? Because he doesn't any more" and so on.

How do other people address this issue?

I'm curious to see if someone else arrives at the same solution I did.

UPDATE 1: onedaywhen suggests the same thing which occurred to me -- simply delete all the old entries, correct or not, and INSERT new ones.

UPDATE 2: potatopeelings suggests adding some code to the form which stores the original value of the field which can be compared with the new value on submit.

Answer 1

I hope this answer will help http://stackoverflow.com/questions/2273815/if-exists-before-insert-update-delete-for-optimization/2275340#2275340

Answer 2

Logically, an UPDATE is a DELETE followed by an INSERT (consider that SQL Server triggers can access logical tables named inserted and deleted but there is no updated table). So you should be able to hit the database only twice i.e. first DELETE all rows (correct or otherwise) for Bob, second INSERT all correct rows for Bob.

If you want to hit the database only once, consider using Standard SQL's MERGE, assuming your DBMS supports it (SQL Server introduced it in 2008).

Answer 3

Assuming the UI is a checkbox grid (1. in Ismail comment in the question)

           MA      PhD    
Alice      x 
Bob                 x

where the x represents checked boxes. I'd go with using the front-end script to send only the changes back to the server. Then doing the INSERTs and DELETEs in the People-to-degrees under a single transaction, or a MERGE (as pointed out in Ismail's link)

BEGIN TRAN
INSERT query
DELETE query
COMMIT

You would pass the INSERT (and DELETE) query a list of people ID, degree ID pairs like. For your example, the INSERT query would be the single pair (2,2) and for the DELETE query the single pair (2,1).