Best Practices for updating multiple check boxes on a web form to a database

Question

A sample case scenario - I have a form with one question and multiple answers as checkboxes, so you can choose more than one. Table for storing answers is as below:

QuestionAnswers
(
  UserID int,
  QuestionID int,
  AnswerID int
)

What is the best way of updating those answers to the database using a stored proc? At different jobs I've seen all spectrum, from simply deleting all previous answers and inserting new ones, to passing list of answers to remove and list of answers to add to the stored proc.

In my current project performance and scalability are pretty important, so I'm wondering what's the best way of doing it?

Thanks! Andrey

Answer 1

If the questions are always the same, then you'd never delete anything - just run an update query on all changed Answers.

Update QuestionAnswers
SET AnswerID = @AnswerID
WHERE UserID = @UserID AND QuestionID = @QuestionID

If for some reason you still need to do some delete/insert - I'd check which QuestionIDs already exist (for the given UserID) so you do a minimum of Delete/Insert.

Updates are just far faster than Delete then Insert, plus you don't make any identity columns skyrocket.

I presume you load the QuestionAnswers from DB upon entering the page, so the user can see which answers he/she gave last time - if you did you already have the necessary data in memory, for determining what to delete, insert and update.

Answer 2

Andrey: If the user (userid=1) selects choices a(answerid=1) & b(answerid=2) for question 1(questionid=1) and later switches to c (a-id=3) & d(a-id=4), you would have to check, then delete the old and add the new. If you choose to follow the other approach, you would not be checking if a particular record exists (so that you can update it), you would just delete old records and insert new records. Anyways, since you are not storing any identity columns, I would go with the latter approach.

Answer 3

If I had a choice of table design, and the following statements are true:

• You know the maximum choices count per question/
• Each choice is a simple checked/unchecked.
• Each answer be classified as correct/wrong rather than marked by some scale. (Like 70% right.)

Then considering performance I would considered the following table instead of the one you presented:

QuestionAnswers
(
  UserID int,
  QuestionID int,
  Choice1 bool,
  Choice2 bool,
  ...
  ChoiceMax bool
)

Yes, it is ugly in terms of normalization but that denormalization will buy performance and simplify queries -- just one update/insert for one question. (And I would update first and insert only if affected rows equals to zero.)

Also detecting whether the answer was correct will be also more simple -- with the following table:

QuestionCorrectAnswers
(
  QuestionID int,
  Choice1 bool,
  Choice2 bool,
  ...
  ChoiceMax bool
)

All you need to do is just to lookup for the row in QuestionCorrectAnswers with the same combination of choices as user answered.

Answer 4

Hello,

It is a simple solution: Every [Answer] should have integer value (bit) and this value is unique for current Question. For example, you have Question1 and four predefined answers:

[Answer] [Bit value]

answer1 0x00001

answer2 0x00002

answer3 0x00004

answer4 0x00008 ...

So, you SQL INSERT/UPDATE will be:

declare @checkedMask int
set @checkedMask = 0x00009 -- answer 1 and answer 4 are checked
declare @questionId int
set @questionId = 1

-- delete

delete
--select r.* 
r 
from QuestionResult r 
    inner join QuestionAnswer a  
        on r.QuestionId = a.QuestionId and r.AnswerId = a.AnswerId
where r.QuestionId = @questionId
 and (a.mask & @checkedMask) = 0

-- insert

insert QuestionResult (AnswerId, QuestionId)
    select
        AnswerId,
        QuestionId
    from QuestionAnswer a
       where a.QuestionId = @questionId
         and (a.mask & @checkedMask) > 0
         and not exists(select AnswerId from QuestionResult r 
                where r.QuestionId = @questionId and r.AnswerId = a.AnswerId)

Answer 5

I disagree that regent's answer is denormalized. As long as each answer is not dependent on another column, and is only dependent on the key, it is in 3rd normal form. It is no different than a table with the following fields for a customer name:

CustomerName ( name_prefix name_first name_mi name_last name_suffix city state zip )

Same as

QuestionAnswers ( Q1answer1 Q1answer2 Q1answerN )

There really is no difference between the "Question" of name and the multiple answers which may or may not be filled out and the "Question" of the form and the multiple answers that may or may not be selected.

Answer 6

Sorry to resurect an old thread. I would have thought the only realistic solution is to delete all responses for that question, and create new rows where the checkbox is ticked. Having a column per answer may be efficient as far as updates go, but the inflexibility of this approach is just not an option. You need to be able to add options to a question without having to redesign your database.

Just delete and re-insert. Thats what databases are designed to do, store and retrieve lots of rows of data.