design question - storing questions and answers for various question types
law...@gmail.com
I'm working on a database that stores data for a survey. We are
storing several different types of surveys, each of which can have any
number of different question types. For instance, a survey could have
a multiple choice question, a yes/no question, numerical question, or
a free-form text answer. When a user logs on to fill out a survey, the
data from the survey template will be copied to a set of tables which
look basically the same, except that they will store the survey
questions and the answers from the user.
Each question type would have a different field or fields for storing
the answer. For instance, a yes/no question would require a binary
field to store the answer, and a single field for the question
itself. However, to store a multiple choice question, I need a field
for the question, a related table for an arbitrary number of pre-
written answer, and then a field for which answer the user chose.
I'm trying to figure out the optimal way to store the questions ( in
survey template tables ) and their user-generated answers.
It seems to me that I have two choices: (1) I can have one table that
has field for all of the question types, and most of fields will be
empty, because each row will only need one or two fields to store a
question. But, it seems I run the risk of storing data in the wrong
field, if I don't code this carefully.
survey_templates
-----------
id
survey_template_questions
------------------------
id
survey_id
question (text)
question_type ( enum yes_no, yes_no_why_not, etc)
sort_order (int)
yes_no ( binary)
why_not (text)
numerical_answer (int)
freeform_answer (text)
one_though_five (enum 1-5)
survey_template_question_multiple_choices
---------------------------------------------------
multiple_choice_question_id
choice ( text )
sort_order
Or, (2) I can have a 'questions', which can be related to various
child tables by a 'question_type' field. The child tables would be
something like 'questions_yes_no', 'questions_multiple_choice', and
then figure out the joins programmatically. I have less of a chance of
getting the wrong data in the wrong fields with careless code.
survey_templates
-----------
id
survey_template_questions
------------------------
id
survey_id
question_type
sort_order
survey_template_question_yes_no
-----------------------------------
survey_template_question_id
yes_no ( binary)
survey_template_question_numerical_answer
-------------------------------------------
survey_template_question_id
answer(int)
survey_template_question_yes_no_if_not_why
-------------------------------------------------------------
survey_template_question_id
yes_no ( binary)
why_not (text)
survey_template_question_freeform
------------------------------------------------
survey_template_question_id
answer (text)
survey_template_question_multiple_choice
----------------------------------------------
survey_template_question_id
question
survey_template_question_multiple_choice_answers
---------------------------------------------------
multiple_choice_question_id
answer
sort_order
Are either of these preferred? Is there a better solution?
Captain Paralytic
Thinking of it another way, a Yes/No question is just a multiple
choice question with only 2 choices.
law...@gmail.com
That's a wise observation, but the multiple choice questions I was
thinking of was where a user can choose zero, some or all -- "check
all that apply". So I might have the same method for storing the
possible answers in the template, I would have to have a different
method for storing the user's answer, unless I want to risk having a
both-yes-and-no answer possible in the database.
Paul Lautman
Just have a field in the question record that states whether the answer
can/must be 1, exactly n, at least n, more than n, all