CODEWITHSUNDEEP
mysqlsqlrdbmsrdbms-agnostic
user151841
user151841

Reputation: 18046

'questions' and 'answers' with multiple answers

This question is related to this post: SQL design for survey with answers of different data types

I have a survey app where most questions have a set of answers that are 1-5. Now we have to do questions that could have a variety of different answer types -- numeric, date, string, etc. Thanks to suggestions from stack, I went with a string column to store answers. Some questions are multiple choice, so along with the table 'questions', I have a table 'answers' which has the set of possible answers for a question.

Now: how should I store answers for a question that is "pick all that apply"? Should I make a child table that is "chosen_answers" or something like that? Or should the answers table have a 'chosen' column that indicates that a respondent chose that answer?

Upvotes: 0

Views: 1581

Answers (5)

longneck
longneck

Reputation: 12226

these survey questions always have one, universal answer: it depends on what you want to do with the answers when you're done.

for example, if all you want to to is keep a record of each individual answer (and never do any totaling or find all users that answered question x with answer y), then the simplest design is to denormalize the answers in to a serialized field.

if you need totals, you can probably also get away with denormalized answers in to a serialized table if you calculate the totals in a summary table and update the values when a quiz is submitted.

so for your specific question, you need to decide if it's more useful to your final product to store 5 when you mean "all of the above" or if it's more useful to have each of the four options individually selected.

Upvotes: 0

Charles Bretana
Charles Bretana

Reputation: 146449

Another option, (And I've seen cases where this was how questions like this were scored as well), is to treat each possible answer as a separate Yes/No question, and record the testee's response (Chose it, or didn't) as a boolean...

Upvotes: 0

David Webb
David Webb

Reputation: 193686

As you're not going to have many options selected I'd be tempted to store the answers as a comma-separated list of values in your string answer column.

If the user is selecting their answers from a group of checkboxes on the web page with the question (assuming it is a web app) then you'll get back a comma-separated list from there too. (Although you won't just be able to compare the lists as strings since the answer "red,blue" is the same as "blue,red".)

Upvotes: 0

Dan Loewenherz
Dan Loewenherz

Reputation: 11236

I have two suggestions.

  1. Normalize your database, and create a table called question_answer, or something that fits more in line with the nomenclature of your schema. This is how I would lay it out.

    CREATE TABLE question_answer (
    id INT NOT NULL AUTO INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    question_id INT NOT NULL,
    answer_id INT NOT NULL
);

  2. Create five columns in your answers table, each of which refers to a specific answer. In MySQL I would use set these columns up as bit(1) values.

IMHO, unless you see the number of choices changing, I would stick with option 2. It's a faster option and will most likely also save you space.

Upvotes: 0

Jimmy
Jimmy

Reputation: 9815

a possible solution is a UsersAnswers table with 4 columns: primary key, user's id, question's id, and answer's id

with multiple entries for any questions where more than one answer can be selected

Upvotes: 3

Related Questions