Is there a better way to structure a SQL table handling User Questionaires?

Question

I have a disclosure form that requires a user to enter yes/no answers for a series of around 30 questions.

Is there a better way to create the table than having 30 columns that correspond the questions?

Below is my MS-SQL script, but the question relates more to structure than syntax.

CREATE TABLE Questionaire(
    QuestionaireID int IDENTITY(1,1) NOT NULL,
    UserID int NOT NULL,
    Q1 bit NOT NULL,
    Q2 bit NOT NULL,
    Q3 bit NOT NULL,
    ... etc ...
    ... etc ...
    Q30 bit NOT NULL
)

-- and to store the questions relating to Q1, Q2, etc...
CREATE TABLE Questions(
    QuestionID int IDENTITY(1,1) NOT NULL,
    Question varchar(30) NOT NULL
)

Answer 1

To fully normalize, you might want to consider a structure like this:

Table Questionaire(
    QuestionaireID...
    QuestionaireName...

Table Questions(
    QuestionaireID...
    QuestionID...
    QuestionName...

Table Response(
    QuestionaireID...
    ResponseID...
    UserID...

Table Answers(
    AnswerID...
    ResponseID...
    QuestionID...
    Answer...

This provides higher information fidelity, as you can capture data in more dimensions - at the response level, the individual answer level, as well as future-proofing yourself for changes to the system.

Answer 2

well, there are 2 better ideas i can think of:

1. store a vector (i.e. a string/byte array variable containing all results), and handle everything related to the data in your program (this way you're more limited on SQL queries)

2. store the key/value pair, keyed by survey-id, e.g.

   1134 age 68

   1134 prefer chocolate

   1134 width 6"

   1135 age 31

   1135 prefer vanilla

   1135 width 3.2"

it depends on what you want to do with the results. but this is more "correct" than what you've suggested, since with my last option you're less likely to run into trouble

Answer 3

1. Do you want to re-use questions in different questionaires?
2. Do you want to have users be able to take more than one questionaire?
3. Do you ever want it to be anything but yes/no answers, like multiple choice?

If yes to all 3, I'd do it like this:

Table Questionaire(QuestionaireID, Name, Description)

Table Questions(QuestionID, Name)

Table QuestionResponses(QuestionResponseID, QuestionID, ResponseText)

Table QuestionaireQuestions(QuestionaireID, QuestionID)

Table UserQuestionaire(UserQuestionaireID, QuestionaireID, UserID)

Table UserResponses(UserQuestionaireID, QuestionResponseID)

Now you can define a list of questions, add them to one to many questionaires, they can have any number of responses, and users can log a questionaire and any responses they pick.