Voting system questions

Question

I'm having some trouble approaching a +1/-1 voting system in PHP, it should vaguely resemble the SO voting system. On average, it will get about ~100 to ~1,000 votes per item, and will be viewed by many.

I don't know whether I should use:

• A database table dedicated for voting, which has the userid and their vote... store their vote as a boolean, then calculate the "sum" of the votes in MySQL.
• A text field in the "item" table, containing the uids that already voted (in a serialized array), and also a numeric field that contains the total sum of the votes.
• A numeric field in the "item" table, that contains the total sum of the votes, then store whether or not the user voted in a text field (with a serialized array of the poll id).
• Something completely different (please post some more ideas!)

Answer 1

I'd probably go with option 3 that you've got listed above. By putting the total number of votes as another column in the item table you can get the total number of votes for an item without doing any more sql queries.

If you need to store which user voted on which item I'd probably create another table with the fields of item, user and vote. item would be the itemID, user would be the userID, vote would contain + or - depending on whether it's an up or down vote.

I'm guessing you'll only need to access this table when a user is logged in to show them which items they've voted on.

Answer 2

I recommend storing the individual votes in one table.

In another table store the summary information like question/poll ID, tally

Do one insert in to the individual votes table.

For the summary table you can do this:

$votedUpOrDown = ($voted = 1) ? 1 : -1;
$query = 'UPDATE summary SET tally = tally + '.$votedUpOrDown.' WHERE pollid = '.$pollId;