Database structure for voting system with up- and down votes

Question

I am going to create a voting system for a web application and wonder what the best way would be to store the votes in the (SQL) database.

The voting system is similiar to the one of StackOverflow. I am pondering now if I should store the up and down votes in different tables. That way it is easier to count all up votes resp. down votes. On the other hand I have to query two tables to find all votes for an user or voted item.

An alternative would be one table with a boolean field that specifies if this vote is an up or down vote. But I guess counting up or down votes is quite slow (when you have a lot of votes), and an index on a boolean field (as far as I know) does not make a lot of sense.

How would you create the database structure? One or two tables?

Answer 1

Regarding the comments, we found the solution that best fits to Zardoz

He does not want to always count votes and needs as much details as possible. So the solution is a mix of both.

1. Adding an integer field in the considered table to store vote counts (make sure there won't be overflows).
2. Create additional tables to log the votes (user, post, date, up/down, etc.)

I would recommend to use triggers to automatically update the 'vote count field' when inserting/deleting/updating a vote in the log table.