The optimal way to store multiple-selection survey answers in a database

Question

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.

Right now, I have a table that looks like this:

survey_answers

id          PK
eid
sesid
intvalue    Nullable
charvalue   Nullable

id = unique value assigned to each row

eid = Survey question that this answer is in reply to

sesid = The survey 'session' (information about the time and date of a survey take) id

intvalue = The value of the answer if it is a numerical value

charvalue = the value of the answer if it is a textual representation

This allowed me to continue using MySQL's mathematical functions to speed up processing.

I have however found a new challenge: storing questions that have multiple responses. An example would be:

Which of the following do you enjoy eating? (choose all the apply)

• Girl Scout Cookies
• Bacon
• Corn
• Whale Fat

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:

survey_element_options

id        PK
eid
value

id = unique value associated with each row

eid = question/element that this option is associated with

value = textual value of that option

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.

---

EDIT

for anyone interested, here is the approach I ended up taking (In PhpMyAdmin Relations View):

And a rudimentary query to gather the counts for a multiple select question would look like this:

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

Answer 1

This really depends on a lot of things.

1. Generally, storing lists of comma separated values in a database is bad, especially if you plan to do anything remotely intelligent with that data. Especially if you want to do any kind of advanced reporting on the answers.
2. The best relational way to store this is to also define the answers in a second table and then link them to the users response to a question in a third table (with multiple entries per user-question, or possibly user-survey-question if the user could take multiple surveys with the same question on it.

This can get slightly complex as a a possible scenario as a simple example:

Example tables:

• Users (Username, UserID)
• Questions (qID, QuestionsText)
• Answers (AnswerText [in this case example could be reusable, but this does cause an extra layer of complexity as well], aID)
• Question_Answers ([Available answers for this question, multiple entries per question] qaID, qID, aID),
• UserQuestionAnswers (qaID, uID)

Note: Meant as an example, not a recommendation