We have different survey types. Each survey type has its own unique sections, and each section has its own unique questions. (Questions are not currently re-used by different survey types and may never be). For questions that are answered by tickboxes ('tick one only' or 'tick all that apply'), there are predefined answers, which are listed in the form_possible_answers table. The right side of the image shows the table relationships for this. Bridge table form_questions_answers_bridge determines which of the possible answers can belong to a particular question. (E.g. 'What condition is your car in?' will only be linked to the 'Excellent', 'Moderate', 'Poor' answers.)
When a form is submitted, details such as the form type and submission date are stored in the form_submissions table. This is where it gets complicated. There are 3 question types: a question that can have a single predefined answer (answered by ticking a single tickbox), a question that can have multiple predefined answers (answered by ticking all tickboxes that apply), and a question that is answered by user text input. I have a submitted_questions table that relates all submitted questions to the form submission. For text-answer questions (knowledge obtainable from the form_questions table), there is a text_answers table. For questions that are answered via a checkbox (single or multiple) there is a multiple_answers table that references the submitted question id and predefined answer.
The multiple_answers table references both the form_questions and form_possible_answers tables. One may question why I then have a bridge table form_questions_answers_bridge. It is a lookup--so I can enforce a constraint when writing data is attempted. (E.g. If the lookup/bridge says the answer 'no' is not possible for 'what color shirt?', then the data won't be written.) Is this a just argument?
