Say I have the following normalised structure: 
TABLE 'People':

NAME   ID
---------
Alice  1
Bob    2

TABLE 'Qualifications':

QUAL   ID
---------
MA     1
MSc    2
PhD    3

TABLE 'people_who_have_qualifications':

PERSON QUAL
1      1 # Alice has an MA
1      3 # Alice also has a PhD
2      2 # Bob has an MSc.

[download]
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
kids today don't know what line-noise IS ...

In reply to OT: updating database question by Cody Pendant

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.