title: The optimal way to store multiple-selection survey answers in a database
body: <p>I'm currently working on a survey creation/administration web application with PHP/MySQL. I have gone through several revisions of the database tables, and I once again find that I may need to rethink the storage of a certain type of answer.</p>
  
  <p>Right now, I have a table that looks like this:</p>
  
  <p><strong>survey_answers</strong></p>
  
  <pre><code>id          PK
  eid
  sesid
  intvalue    Nullable
  charvalue   Nullable
  </code></pre>
  
  <blockquote>
    <p>id = unique value assigned to each row</p>
    
    <p>eid = Survey question that this answer is in reply to</p>
    
    <p>sesid = The survey 'session' (information about the time and date of a survey take) id </p>
    
    <p>intvalue = The value of the answer if it is a numerical value</p>
    
    <p>charvalue = the value of the answer if it is a textual representation</p>
  </blockquote>
  
  <p>This allowed me to continue using MySQL's mathematical functions to speed up processing.</p>
  
  <p>I have however found a new challenge: storing questions that have multiple responses.
  An example would be:</p>
  
  <p><strong><em>Which of the following do you enjoy eating? (choose all the apply)</em></strong></p>
  
  <ul>
  <li>Girl Scout Cookies</li>
  <li>Bacon</li>
  <li>Corn</li>
  <li>Whale Fat</li>
  </ul>
  
  <p>Now, when I want to store the result, I'm not sure of the best way to handle it.
  Currently, I have a table just for multiple choice options that looks like this:</p>
  
  <p><strong>survey_element_options</strong></p>
  
  <pre><code>id        PK
  eid
  value
  </code></pre>
  
  <blockquote>
    <p>id = unique value associated with each row</p>
    
    <p>eid = question/element that this option is associated with</p>
    
    <p>value = textual value of that option</p>
  </blockquote>
  
  <p>With this setup, I then store my returned multiple selection answers in 'survey_answers' as strings of comma separated id's of the element_options rows that were selected in the survey. (ie something like "4,6,7,9") I'm wondering if that is indeed the best solution, or if it would be more practical to create a new table that would hold each answer chosen, and then reference back to a given answer row which in turn references back to the element and ultimately the survey.</p>
  
  <hr>
  
  <p><strong>EDIT</strong></p>
  
  <p>for anyone interested, here is the approach I ended up taking (In PhpMyAdmin Relations View):</p>
  
  <p><img src="https://i.stack.imgur.com/LPzn9.png" alt="Database Relationships and Table Structure"></p>
  
  <p>And a rudimentary query to gather the counts for a multiple select question would look like this:</p>
  
  <pre><code>SELECT e.question AS question, eo.value AS value, COUNT(eo.value) AS count
  FROM survey_elements e, survey_element_options eo, survey_answer_options ao
  WHERE e.id = 19
  AND eo.eid = e.id
  AND ao.oid = eo.id
  GROUP BY eo.value
  </code></pre>