question

essence388 avatar image
essence388 asked

Can you recommend a database design for survey questions and answers that would allow storing all of its information?

I have to create a database that stores the answers of the survey. The survey has three types of questions: multiple choices, ranking and written questions. The survey consists of category and under some of the categories, there are subcategories. Under some categories or subcategories there are questions. Some questions has subquestions. The database should store the respondent information. I am confused and I don't know how to design a proper database that captures the answers of the three questions types. What I have up to now is the following: Respondents: RespondentID, Name, OrgCode Departments: OrgCode, DepartmentName Category: CategoryID, CategoryName SubCategory: SubCategoryID, SubCategoryName, CategoryID Questions: QuestionID, QuestionDesc SubQuestions: SubQuestionID, SubQuestionDesc, QuestionID Answers: AnswerID, AnswerDesc More details on the survey questions: Multiple choice single answer (radio button) and the number of answers could be 2 or 4 or 5 Ranking is similar to multiple choice question with multiple answers (think like checkbox) but the main question will be mainly about ranking 7 areas from 1 to 3 Written question is like asking the participants to add comments or something like this. **So how to modify the database design to capture the answers of the three questions types with relating them to the respondent?** Note that some questions have subquestions and sometimes not, so how to capture the answers for the questions that don't have subquestions and for those which have subquestions?
sql-server-2008database-design
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Grant Fritchey avatar image
Grant Fritchey answered
For the questions, I'd change the structure. Get rid of the sub-questions table. Instead, make the questions table self-referencing. Meaning, you can add a question to a question. You just have to have a nullable column that you can create a foreign key reference back to the primary key of the question. This will allow you to relate one question to another, but optionally. For the answers... that's slightly tougher. Since each of the three answer types is different in structure, I'm inclined towards setting a question type, then based on that type, refer to one of three tables that allow you to define the answers to the question for each type of question. A simple table for the written answers. The multiple choice and ordering answers are tougher. But, the thing is, do you need to simply save the answers provided or do you have to define the entire test for display as well as capture the answers? If the former, you just need to store one record for each question. If the latter, you actually need two structures, one for defining the test, and one for recording the answers. Assuming you're defining the test, for the multiple choice, create two tables, one that is simply a list of answers, and the other an interim table, also known as a many-to-many table, that joins between a given question, and a list of answers. This can provide any number of answers to a given question. For the answers to the question, just record the answer selected in a child table that maps between the answer, the question, and the test taker.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.