CODEWITHSUNDEEP
c#sql-serverdatabasedatabase-designsurvey
quents
quents

Reputation: 121

Storing multiple choice answers of a survey in database

I try to create a survey app using asp.net (c#) based on ms sql server. The user can see multiple choice, single choice, and textbox answer choices to input. I am able to store textbox and single choice answers in my database but I am stuck on storing multiple answers.

My design is the following:

employee : (id, username)

survey_title : (id, title, description, creation_data)

survey_answer_type : (id, type_name)

survey_question : (id, question, survey_id, survey_answer_type_id)

survey_answer : (id, question_id, answer) -all answer choices (answer is null if answer type is textbox)-

survey_eanswer : (id, employee_id, answer_id, str_answer) -answers of employees (str_answer is used to store textbox answers)-

I could not find a way to store multiple answers in my survey_eanswer table, and I know that comma separated inputs are not a good design strategy.

Thanks.

Upvotes: 1

Views: 3090

Answers (1)

mcdikki
mcdikki

Reputation: 109

A good design would be to have a table

survey_question_options: (ID,QUESTION_ID,OPTION)

|ID|question_id|option|
|1|1|red|
|2|1|blue|
|3|1|white|
|4|2|small|
|5|2|medium|
|6|2|large|

And then just save the id of the choosen option. Save each choosen option as own separate db entry.
This enables you to use the standard sql operations to join/group and evalute your survey at the end.

Example, you want to now how many users have choosen medium:

SELECT Count(*)
FROM survey_answers
WHERE question_ID = 2 AND answer = 2

Or, more complex but meaning the same:

SELECT Count(survey_answer.id)
FROM survey_answers
LEFT JOIN survey_question_options ON
    survey_question_options.ID = survey_answers.Answer AND
    survey_question_options.question_ID = survey_answers.question
WHERE survey_answer.question_ID = 2 
    AND survey_question_options.option LIKE 'medium'

Upvotes: 1

Related Questions