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
