Your way would work, but it's not very flexible or expandable. For a more complete voting system, that allows multiple questions to vote on etc., probably it's best to store the question number and the id of the answer given by the voter eg:
Code: Select all
CREATE TABLE question (
id_question int(5) not null auto_increment,
description_question varchar(255) not null,
primary key (id_question));
CREATE TABLE answer (
id_question int(5) not null,
id_answer int(2) not null auto_increment,
description_answer varchar(255) not null,
primary key(id_question, id_answer))
engine='myISAM';
CREATE TABLE vote (
id_user int(8) not null,
id_question int(5) not null,
id_answer int(2) not null,
primary key (id_user, id_question));
CREATE INDEX idx_vote ON vote (id_question, id_answer);
The id_user in the vote table is to prevent double voting (could be simply an IP Address, could be an actual log-in id)
To get the number of votes out, you'd do a simple
Code: Select all
SELECT q.description_question, a.description_answer, count(v.id_answer) as votes
FROM question q LEFT JOIN answer a
ON q.id_question = a.id_question
LEFT JOIN vote v
ON v.id_question = a.id_question AND v.id_answer = a.id_answer
WHERE q.id_question = ***id of question***
GROUP BY q.description_question, a.description_answer;
Some test data:
Code: Select all
INSERT INTO question VALUES (NULL, 'What is your favourite vowel?');
INSERT INTO answer VALUES (1, NULL, 'a'), (1, NULL, 'e'), (1, NULL, 'i'), (1, NULL, 'o'), (1, NULL, 'u');
INSERT INTO vote VALUES (1, 1, 1), (2, 1, 4), (3, 1, 4), (4, 1, 2), (5, 1, 5), (6, 1, 4), (7, 1, 4), (8, 1, 2), (9, 1, 2), (10, 1, 1);
and the query outputs:
Code: Select all
+-------------------------------+--------------------+-------+
| description_question | description_answer | votes |
+-------------------------------+--------------------+-------+
| What is your favourite vowel? | a | 2 |
| What is your favourite vowel? | e | 3 |
| What is your favourite vowel? | i | 0 |
| What is your favourite vowel? | o | 4 |
| What is your favourite vowel? | u | 1 |
+-------------------------------+--------------------+-------+