Efficient way to update SQL 'relationship' table
- by AmbroseChapel
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.
