OT: updating database questionby Cody Pendant (Prior)
|on Jul 22, 2007 at 06:59 UTC||Need Help??|
Cody Pendant has asked for the wisdom of the Perl Monks concerning the following question:
Say I have the following normalised structure:
Now say I have a web interface to edit/update those relationships, which brings all of a person's details into one form.
When Bob gets his PhD or I realise Alice's MA was a data entry error and should be a BA, I need to edit their record and save in a way that updates that 'people_who_have_qualifications' table.
The simplest way to do that, it seems to me, is to just delete everything and then re-enter it. First pass, Alice has no qualifications at all. Then, second pass, we read off the CGI param()s we got from our checkboxes and put her qualifications back in.
Now, it's kind of inefficient, because every time we edit Alice's record, we're deleting everything and re-inserting it. And of course if the network/db server/script hits a problem between the first and second pass we've got missing data.
But if I don't do that I have to tiresomely look up and compare every value already in the db with the values in the CGI param()s. "Is this value there in the db and in the form? Leave it alone. Is it there in the db but not in the form? Delete it. Is it in the form but not in the db? Insert it." Lots more database activity in total, lots more scripting, etc.
I guess what I'm asking is, Is the "delete all, re-insert" method good laziness, à la Larry, or bad?
Nobody says perl looks like line-noise any more