I'm developing a system to allow users to vote on different subjects using a Yes / No / Maybe question set.
Each question will be weighted as 2 (Yes) / 0 (No)/ 1 (Maybe) NOTE(The users will be voting multiple times, potentially huge amounts)
My plan is to store the answers in a mysql db.
Do I store each vote separately? (ID/Vote/)
What is a proper query to tally the vote results?
What is the most efficient way to store and retrieve the results? Should I have a table that stores the vote scores?
I'm thinking you would only need to store each individual vote for auditing or logging purposes.
I'm guessing you could do it all in one row, per question.
so:
Voteid Questionid VoteTally
And you could then just run an update on the votetally. when yes, add 2, when maybe, add 1, when no, do nothing.
Are you going to display the results back to the user, using a status bar? if so, then yeah you would need to store the info.
I wouldn't store the weighted values in the DB, instead I'd use the DB as a tally system for 3 different types of votes. You can have 1 table with 3 fields in it
Fields: Yes, No, Maybe, all integers. Initially start the field values at 0. Every time a vote comes in, increment the appropriate field value.
After, do the weighted math in your code. As simple as multiplying the number, say in the "yes" field by 2 to get the weighted result, as an example.
This is of course if the vote totals matter, and you don't care about storing each individual voters preference separately.
To ensure uniqueness I'd make the user sign up up first, that way you can flag the user as already voted for that particular poll, so they can't vote again. They can always sign up again, though. Unfortunately it's tough to get around that, as they can simply use another computer, a proxy server, or a lot of people have dynamic IP's which would simply require a modem reboot to vote again, even if you did store IP's to limit such situations.
One thing to consider: storing each vote separately makes it easier to restrict voting by IP.
main reason you would need to do #1 is to ensure no duplicate voting (e.g. store IP address where each vote was cast with this record).
if your looking for great performance, you can do what digg.com (according to a lecture) does for some things of this nature. they store all votes in a memcached node with IP to prevent duplicates with an expiration of ~24 hrs. they then have a daemon/cron job come and tally the votes and store them in an aggregated format at the persistent/db layer.