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?

On 1 Oct, 15:26, lawp...@gmail.com wrote:
> 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?

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

On Oct 1, 11:41 am, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> On 1 Oct, 15:26, lawp...@gmail.com wrote:
>
>
>
> > 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?
>
> Thinking of it another way, a Yes/No question is just a multiple
> choice question with only 2 choices.

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.

lawpoop@gmail.com wrote:
> On Oct 1, 11:41 am, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>> On 1 Oct, 15:26, lawp...@gmail.com wrote:
>>
>>
>>
>>> 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?
>>
>> Thinking of it another way, a Yes/No question is just a multiple
>> choice question with only 2 choices.
>
> 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.

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