Combining SQL Insert and Delete

Question

Ok here is the question

I have three tables

events
- id
- name

questions
- id
- event_id
- name

answers
- id
- question_id
- description

While generating the add form, i can simply search for all the questions belonging to particular event, and show the questions in single HTML form.

But consider the edit scenario. An event manager can in due time add or delete some questions from an event. So in such case, when the user tries to edit already posted answer, I generate the HTML form with the new questions as blank field and existing answers are prefilled input boxes. How can I handle the submission of this form?

When the user submits, should i delete all past answers and do a INSERT on the answers table for all the answer values? or is it a good practice to UPDATE existing answers first and INSERT only new values?

The former obiviously being easier with DELETE followed by INSERT. While the later is somewhat tedious. The problem with former solution is that the id of the answers table will increase every time..

Answer 1

Some people favour the delete/insert approach because, like you say, it is simpler.

Personally I think the update/insert/delete approach, while more work, is more correct.

If you do updates you can then have an audit trail of changes to a particular item. With just insert/delete it's either much harder or plain impossible to have that kind of linkage and history.

As for how to handle the submission of the form, for fields that can be updated (ie they're existing records), you need to be able to identify the field somehow. Personally I just encode something like the primary key in the field name.

If you do this you must of course ensure that you don't have a security hole by validating that the ID supplied is valid and the edit allowed ie never trust the client.

This could take the form of:

<input type="text" name="name_117" value="Some value">
<input type="text" name="name_118" value="Some other value">
<input type="text" name="name_1243" value="Yet another value">

and you have to process all the input parameters, decode the identifier and act accordingly.

Lastly, another problem with insert/delete is that you can't do it (or it just gets really hard) if the items you're deleting/inserting relate to other tables in the database. If you have a question table and store the answers people give, normally you'll reference the question as a foreign key. You lose that association if you delete/insert instead of updating.

Answer 2

First, the ID shouldn't be being used for anything anyway, so disregarding the incrementation is a good reminder.

I would prefer to deal with the primary question, however, cnnceptually, everything else being equal. Is the user in fact establishing a different set of questions with their edits? Then save them as a new set. Or is the original set intended to retain its conceptual identity? Then update/delete/insert. Which seems to be more the case.

In terms of securing the integrity of the question/answer set, I think of sessions having that responsibility. You should consider validating the question set against that associated with the session.

Answer 3

Online quiz applications usually lock down a quiz once it is live. It is a messy problem to deal with completed answer sets versus a changing definition of questions. If your application can handle it, I would favor not allowing questions to change once an event is first published.

Answer 4

Agree with Cletus post, writing seperately for an extra reason and an idea:

The delete/insert approach has a concurrency issue, when your website gets some heavy traffic. Someone else could do an insert after your delete, and then you'll end up with multiple answer lists per user.

MySQL supports the ON DUPLICATE KEY UPDATE syntax. That might be an easy way to combine the insert and the update, if you have an appropriate primary key.