Database structure for voting system with up- and down votes

sql database schema voting-system
Zardoz picture Zardoz · Dec 10, 2010 · Viewed 7.1k times · Source

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

Julio Guerra picture Julio Guerra · Dec 10, 2010

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.

Related questions

How to create a new schema/new user in Oracle Database 11g?

I have applied for an internship in a company and as a question they have asked me to create a schema for their company with certain requirements and mail them the DDL file. I have installed Oracle database 11g Express …

Read More
How do I change db schema to dbo

I imported a bunch of tables from an old sql server (2000) to my 2008 database. All the imported tables are prefixed with my username, for example: jonathan.MovieData. In the table properties it lists jonathan as the db schema. When I …

Read More
PostgreSQL: Create schema in specific database

I need to write an sql script that creates both a new database AND a new schema in the database I just created. How can I do it? Can I somehow change the current database to the new one? Or …

Read More