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 go with a slight variant of the first option:

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.

Replace the boolean with an integer: +1 for an up-vote and -1 for a down-vote.

Then, instead of computing the sum over and over again, keep a running total somewhere; every time there is an up-vote, add one to the total and subtract one every time there is a down-vote. You could do this with an insert-trigger in the database or you could send an extra UPDATE thing SET vote_total = vote_total + this_vote to the database when adding new votes.

You'd probably want a unique constraint on the thing/userid pair in the vote tracking table too.

Keeping track of individual votes makes it easy to keep people from voting twice. Keeping a running total makes displaying the total quick and easy (and presumably this will be the most common operation).

Adding a simple sanity checker that you can run to ensure that the totals match the votes would be a nice addition as well.

serialized array: Please don't do that, such things make it very difficult to root around the database by hand to check and fix things, serialized data structures also make it very difficult (impossible in some cases) to properly constrain your data with foreign keys, check constraints, unique constraints, and what have you. Storing serialized data structures in the database is usually a bad idea unless the database doesn't need to know anything about the data other than how to give it back to you. Packing an array into a text column is a recipe for broken and inconsistent data in your database: broken code is easy to fix, broken data is often forever.

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;

Answer 3

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.