SQL Server denormalization question - store user data in 1,3, or 21 tables?

Question

A section of our web site calls for asking the user 20 multiple-choice questions about themselves (a "profile"). This part of the web site will be frequently viewed, and occasionally updated. The web site is expected to develop a high amount of traffic, so some consideration is being given to try and prepare for performance issues.

The 3 ways I can see to store this in the DB are:

1. Create 1 table for each question, each table has QuestionID and Answer, then a CustomerInfo table to store the profile data, with foreign keys mapped to the questions.

2. Question, Question_Type, and Answers tables. Stuff everything into these constructs. My concern here is in particular the 20 or so inserts required to update a profile. Is this going to be a performance hit at high traffic volumes?

3. Denormalized single table, one field per question, hard-code the answer codes in the HTML and/or in a C# object.

I'm leaning towards #2 or #3. What do you think would be the best solution?

Answer 1

I would create the following tables:

Survey (PK:SurveyID)
Question (PK:QuestionID, FK:SurveyID)
QuestionAnswer (PK:QuestionAnswerID, FK:QuestionID)
QuestionResponse (PK:QuestionResponseID, FK:QuestionAnswerID, FK:UserID)

You can minimize the load by doing the inserts in one statement, e.g.:

insert into QuestionResponse
(QuestionAnswerID, UserID)
select 23, 3
union all
select 72, 3
union all
select 488, 3