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@xxxxxxxxx> 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@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
