Hi,
Is there any optimal and generally agreed way to store questionnaire
data in a database?
The questionnaire would have to support both of the following:
- different question types (e.g. What is your name? (free form text)
Are you a smoker? (yes/no checkbox) Are you male or female? (radio
buttons) Select which country you are from (drop-down box).)
- multiple paths (e.g. if a user were asked what their primary mode of
transport is and they answered "a motorbike" they would be asked if
they carry pillion passengers and how many ccs the engine is, whereas
if they said something like walking they would be asked how far they
walk to work and how long it takes)
I have previously had a questionnaire which had 5 tables, questions
and answers and question types, questionnaire and results.
questions
=======
id (serial) [PK]
question (text)
question_type (int)
question_types
===========
id (serial) [PK]
description (text)
answers
======
id (serial) [PK]
answer (text)
next_question_id (int) [FK to questions.id]
questionnaire
==========
id (serial) [PK]
questionnaire_date (timestamp)
results
=====
id (serial) [PK]
questionnaire_id [FK to questionnaire.id]
question_id (int) [FK to questions.id]
answer_id (int)
answer_text (text)
If the question was for free form text, the answer_id would be 0,
which seems a bit kludgey to me. Plus because an answer ID can't be
required due to free form text answers, I can't enforce a foreign key.
Is there a nice elegant solution anyone knows of?
Thanks
Thom