17

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?

medihack
  • 16,045
  • 21
  • 90
  • 134
  • If you are only counting votes on posts, no matter who/when/why, I would put a field 'votes' in the Post table (how to prevent more than one vote?). If you need to count votes with all details (when, who...), you definitely need another table. And if you are counting votes for different entites (posts, comments, videos), I would add another table (eg vote_type). – Julio Guerra Dec 09 '10 at 23:47
  • 1
    @Julio How do you tell if a user has already voted on a particular post? – El Ronnoco Dec 09 '10 at 23:50
  • @El Ronnoco how would I know his specifications? I'm just giving several ways. With the last two solutions you can. – Julio Guerra Dec 09 '10 at 23:52
  • @Julio Ah yes sorry, reading too quickly :) – El Ronnoco Dec 09 '10 at 23:58
  • 1
    @Julio Yes, I want to store as much info as possible about the vote ... who / when / what. Thats why I want at minimum one vote table. But think of the following ... you have ten million votes and want to find out the up votes. That's pretty bad with only having one vote table that stores the up or down vote in a boolean field. And that is why I thought about two tables, but wasn't sure. But as longer as I think about it ... the more I come to the conclusion that two tables is the way to go. One for up and the other for down (at least if you expect a lot of votes). – medihack Dec 10 '10 at 00:02
  • In that case, create a table containing the number of votes (or a vote field) and use the other tables as additional tables to obtain more informations in case of (has this member already voted? who voted from that date to that date, etc.). – Julio Guerra Dec 10 '10 at 00:04
  • If I understand you right then you mean to just store the vote count together with the voted for entity? Good point ... in that case I would never need to count votes in the vote table. But if I want to have the same info for the users too (how often they voted), then I would also need to store that info somewhere (maybe another table or in the user table). – medihack Dec 10 '10 at 00:14
  • 1
    Yes, simply add an integer field. When someone votes, modify that field (+1/-1; be carefull with overflows if you have that much users :p) and log the votes in a table storing for example who voted, the date, up/down, the post, etc. – Julio Guerra Dec 10 '10 at 00:19
  • @Julio, why not copy&paste your comment(s) as answer? I guess I will implement it that way as it seems to result in the lowest SQL query count. – medihack Dec 10 '10 at 00:26

4 Answers4

18

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.

Community
  • 1
  • 1
Julio Guerra
  • 5,523
  • 9
  • 51
  • 75
  • As we can log vote count of both question and answer table, how to create a link between Log table and different entities? Do we need to create a composite key of userid +entityId + entityType on log table? We need to consider to handle the duplicate upvote from same user. – rahulP Jun 26 '23 at 16:07
12

If your votes are just up/down then you could make a votes table linking to the posts and having a value of 1 or -1 (up / down). This way you can sum in a single go.

Gabriele Petrioli
  • 191,379
  • 34
  • 261
  • 317
  • That sounds nice. Thanks for the suggestion. – medihack Dec 10 '10 at 00:04
  • wont this create an N+1 query since you'd have to make a (select sum(value) from post_vote) query N times? What do you think about the accepted answers approach? I am working on something like this myself, and was wondering how I should go about it. – Jaigus Jun 18 '14 at 23:21
  • How do you mean ? When displaying multiple posts ? Then you would do a `group by` like this i.e. `SELECT p.id, p.title, SUM(pv.value) AS votes FROM [posts] p LEFT OUTER JOIN [post_vote] pv ON p.id = pv.post_id GROUP BY p.id, p.title` – Gabriele Petrioli Jun 19 '14 at 08:38
1

https://meta.stackexchange.com/questions/1863/so-database-schema

Worth a look or

http://sqlserverpedia.com/wiki/Understanding_the_StackOverflow_Database_Schema

Community
  • 1
  • 1
PMC
  • 4,698
  • 3
  • 37
  • 57
1

You will need a link table between users and the entities which are being voted on, I would have thought. This will allow you to see which users have already voted and prevent them from submitting further votes. The table can record in a boolean whether it is an up or down vote.

I would advise storing in the voted entity a current vote tally field to ease querying. The saving in size would be negligible if you omitted this.

El Ronnoco
  • 11,753
  • 5
  • 38
  • 65