Database Design For Storing User Votes

For a custom-coded website, I have forums that will let logged in users vote a post up or down, but not allow duplicates. I see a few different ways to handle the database design for this, and I could use a bit of insight about what you think would work best (or better ideas if you know something that might work better):

1. In the post record, create two fields, one storing the users that have upvoted and one storing the users that have downvoted. That way we know if someone has voted and which way they voted. In our program logic we'd have to do some parsing to check whether someone already voted, and it may not be very efficient.

2. Just store the list of people who have already voted and don't worry about whether it was up or down because that will already be calculated and stored in a total field. This means that a user wouldn't be able to change their vote since we don't have the information we need to reverse the operation. It would also mean having to write parsing logic to check whether a user already voted.

3. Create a separate table storing each vote, referencing the user and post tables. This will add another query and possibly get pretty huge if we have lots of active users voting on things, but would probably require the least amount of program code/logic to handle. In this case would it make sense to store the total in the forumpost object so we don't have to run count queries every time the post is displayed and only have to recalculate when a vote is cast?

4. Some other method not mentioned?

I'll be supporting thousands of users and the number of posts will probably hit tens of thousands at the very least (and votes maybe hundreds of thousands), so I'd like to do this in a sensible way from the start.

 

Why would the 3rd option create another query? Yes, it would be an insert (make the index unique).

But the 1st option would probably be bad, you would need to store this as a text field, then have to use php to find out if the user has already voted, then append with mysql to that field, etc.. I can see that becoming the slowest method because of the large text fields. I would just go with option 3 and if the need arises, then you can look at other options.

 

Option 3 for sure.

Set the table up like:

votes:
vote_id
post_id
user_id
vote

This table could get large as you suggested, but this would definitely be the proper way to handle it.

You could also add a trigger to update a totals table every time a vote is cast or changed. This way you wouldn't have to query the votes table every time. When a user goes to vote, you would still check to make sure they haven't voted for that specific post yet.

 

Thank you for the good answers. The more I thought about it the more #1 and #2 sounded like a bad idea. #3 with an update trigger to recalculate totals on insert/update sounds like the magic bullet.