Re: Storing questionnaire data

You may want to look at this article for some ideas:
http://www.varlena.com/GeneralBits/110.php
The article talks about doing dynamic schema design for online
surveys... it's pretty interesting.

Aaron Thul
http://www.chasingnuts.com

On Wed, Oct 22, 2008 at 10:59 AM, Thom Brown <thombrown(at)gmail(dot)com> wrote:
> 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
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>