What's the most efficient SQL Server table structure for this scenario?

Question

People are going to take a survey with 86 questions and the results will need to be kept in a database. There are only two possible answers for each question (1 and 2). Is it more efficient to make a table like this:

ANSWERS
id (int) - primary/identity
user_id (int)
question_number (int)
answer (tinyint)

... which would have 86 records per user? Or like this:

ANSWERS
id (int) - primary/identity
user_id (int)
q1 (tinyint)
q2 (tinyint)
q3 (tinyint)
...
...
...
q84 (tinyint)
q85 (tinyint)
q86 (tinyint)

The results don't have to be compared or tabulated. Just stored for future reference.

Thanks!

Answer 1

It's about time someone posted an interesting question here!



I'd do it this way:



table_questions

QuestionID INT AUTOINCREMENT PRIMARY KEY

QuestionText VARCHAR(255)



table_answers

AnswerID INT AUTOINCREMENT PRIMARY KEY

UserID INT FOREIGN KEY

QuestionID INT FOREIGN KEY

AnswerValue BIT // 0 = 1, 1=2; you could also make it a tinyint if you want



For each user vote, a record is created in table_answers for each question;



To get responses to a specific question from all users:



SELECT q.QuestionID, a.UserID, a.AnswerValue FROM table_questions q INNER JOIN table_answers a ON a.QuestionID = q.QuestionID WHERE q.QuestionID = (id) ORDER BY q.QuestionID, a.UserID



To get responses to questions from a specific user:



SELECT a.UserID, a.AnswerValue, q.QuestionID FROM table_answers a INNER JOIN table_questions q ON a.QuestionID = q.QuestionID WHERE a.UserID = (id) ORDER BY q.QuestionID

Answer 2

Since you are recording peoples answers you are on the right track.



So that you can analyze the results, having each answer result in a seperate field will make it easier by using queries. Since each answer is in its own field.





Since computers now have large disk drives you can afford a little inefficiency as the benefits (mentioned above) would far out weight the reduction in storage space. If you come up with a means to compact several answers into a single INT value by weighted values (each answer corresponds to a bit position in a BINARY WORD ) then you will complicate the analysis of the data and require additional code to extract the answers. This would negate the storage advantage you would gain. You would also need to add code to compress the answers and you would complicate future survey changes.



Another compaction scheme would be to have a single answer field as a VARCHAR and concatenate each letter respons into a single string where position in the string corresponds to a particular question.



Either of these methods will simplify the layout of the table and complicate later analysis

Answer 3

i would just store all of the answers as an XML string, its efficient and will allow you to change the number of survey questions in the future with no problems