Re: Storing questionnaire data

On Wed, Oct 22, 2008 at 03:59:07PM +0100, Thom Brown 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:

Without going EAV (almost always a mistake, this should get you a long
way in the right direction :)
<http://www.varlena.com/GeneralBits/110.php>

Cheers,
David.
> - 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

--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate