title: Database Design for Conditional Questionnaire
body: <p>I am designing a database schema to support a business case in which a user can submit a request (for him/herself or on someone else's behalf). To process and complete the request, the submitter will be <strong>prompted with questions based on their answers to prior questions</strong>. That is to say, the next question is conditional based on the current question's answer.</p>
  
  <p>Each question will have an associated type, which will drive the user form for that particular question. A question of type <strong>boolean</strong> indicates Yes/No radio buttons for the answer. Questions of type <strong>multiple</strong> indicates a multiple choice answer, where users will select one of multiple radio options.</p>
  
  <p>I have two questions:</p>
  
  <ol>
  <li>How can I modify my schema to "link" answers to multiple choice questions? (ie "the following answers are available for question X.")</li>
  <li>How should the answers drive the next question? (ie. "for question #1, if answer A is chosen, then GOTO question 5")</li>
  </ol>
  
  <p>My question_relationships table will let me specify that question 1 is the parent of question 5, and question 5 is the parent of question 6. But I really need the answers to drive this logic.</p>
  
  <pre><code>question
      -id         
      -question_name
      -question_text
      -question_hint
      -question_type (boolean, multiple)  
  
  question_relationship
      -id                 
      -fk_parent_question_id
      -fk_child_question_id
  
  request
      -id         
      -person_id  
      -submitter_id
      -submit_date
      -status 
  
  request_answer
      -id
      -fk_request_id
      -fk_question_id
      -answer_text
      -answer_boolean
  </code></pre>
  
  <p>I have seen the answers in <a href="https://stackoverflow.com/questions/540885/db-design-survey-quiz-with-questions-and-answers">db design - survey/quiz with questions and answers</a>, but I believe that my scenario is a bit different.</p>