design question - storing questions and answers for various question types

design question - storing questions and answers for various question types

Post by lawpoo » Tue, 02 Oct 2007 23:26:51


Hello all -

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?
 
 
 

design question - storing questions and answers for various question types

Post by Captain Pa » Wed, 03 Oct 2007 00:41:09

n 1 Oct, 15:26, XXXX@XXXXX.COM wrote:

Thinking of it another way, a Yes/No question is just a multiple
choice question with only 2 choices.


 
 
 

design question - storing questions and answers for various question types

Post by lawpoo » Wed, 03 Oct 2007 02:11:24

n Oct 1, 11:41 am, Captain Paralytic < XXXX@XXXXX.COM > wrote:

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.

 
 
 

design question - storing questions and answers for various question types

Post by Paul Lautm » Wed, 03 Oct 2007 04:39:13

XXXX@XXXXX.COM wrote:

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