Best way to store multiple question poll results on a per-user basis?

Question

I have a poll that has five 1-5 star ratings and I need to store the values individually for each user. Is there a method similar to bitfields where I can store these enumerated values (1, 2, 3, 4, or 5 for each rating) and easily extract them?

At the moment my best bet would be to store a serialized PHP array, however that would take up a bit of space. Or, create 5 columns, which I would also prefer not to do (wasted space).

Answer 1

My first thought about this is using and storing the values like this:

$a="15540";

The user didn't answer the last question. $a[0] is the value of the first vote which is: 1.

Maybe not the fastest but simple.

Answer 2

Well, whatever you do, definitely don't denormalize by creating a column per possible answer. If you are using a database, the most flexible way would be to store your data in something like this

table polls
poll_id
description

table poll_questions
question_id
poll_id (foreign key to polls)
question_text

table question_answers
answer_id
question_id (foreign key to poll_questions)
answer_text

table user_answers
id
user_id (foreign key to users)
answer_id (foreign key to question_answers)

Easiest to maintain, though not the fastest due to joins you may need to do.

Answer 3

Considering this is a poll you are going to want to get some statistics at some point.

You can either store the answers flat in a CHAR(5) or VARCHAR(5) in which case you will be using MySQL String Functions to get the individual answer value. If you don't need to calculate statistics in the DB then just select the string. In PHP you can access string elements the same way you access array elements with square brackets. $answers = "42133"; $first_answer = $answers[0]; $second_answer = $answers[1]; and so on. You can build the answer string by concating $answers = $first_answer . $second_answer . $third ... or by imploding an array $answers = implode($answer_array);

A simpler approach would be to normalize the DB like Artem suggested.