3

I am trying to create a voting system like Stack Exchange. Should I create a table and insert values like following and then sum up the "vote" or there are some better ways to do this?

Table name: vote

id  userid   article_id  vote
 1    1001      12       1
 2    1002      12      -1
 3    1003      12       1
 4    1002      10      -1

Table name: articles

 article_id     article_title   article_content  article_author
   12              something       something       something     
   10              something       something       something

From the example above the total vote for the article 12 is 1+(-1)+1 = 1

yannis
  • 39,597
black_belt
  • 171
  • You might consider adding a column 'total' to your articles table so you don't have to sum up all votes every time you want to render a page. – Alex May 20 '12 at 12:39
  • @Alex thanks for your reply. Okay lets say I have added a total column to my articles table, but do you think its a good idea to proceed with the way I mentioned above? Because my concern is if I use the VOTE table , it continue to be filled up with huge number of rows. I guess you understand my problem. Please kindly let me know what would be your suggesting regarding this. Thanks :) – black_belt May 20 '12 at 15:27
  • 1
    The problem is if you want to allow the user to vote up then he can change his mind and vote back down. The only way to do that is to track all the votes, just like you designed. Also you need that kind of structure if you plan on having detailed statistics on the votes (when they happen, by whom, etc.). – Alex May 20 '12 at 18:06

1 Answers1

4

"Like Stack Exchange" is a summary requirement that implies a number of details. One of those details is the ability for users to change their votes. Thus you certainly do need a table like vote, with a mechanism to replace the vote.vote value. Another implied detail is a high-performance web interface, which leads to caching and/or denormalization of certain infrequently-changed data, such as the vote total for an article (as Alex suggests). Ensuring those cached/denormalized values are accurate can be difficult. Caching systems are notoriously complex if you always need accurate data, but you might consider invalidating the cached article on every insert/update to the vote table. Similarly, you might want to use a database trigger to apply voting deltas to the articles table on every insert/update to the vote table.

A few unrelated observations:

  • Having columns with the same names as their tables is a code smell, often indicating a lack of data-model planning.
  • You don't need an id column in the vote table. Id columns should only exist in tables where there is no other primary key. The primary key of the vote table should be a composite of userid and article_id.
  • You don't show database constraints, but vote should require the combination of userid and article_id to be unique. You don't want multiple votes from a single user on the same article.
Ross Patterson
  • 10,395
  • 1
    Your comment about id columns is not exactly true, it is a matter of preference. When using EF for example an id column is almost mandatory. You can easily have an id column and then create constraints to enforce whatever you want to. – Alex May 20 '12 at 18:04
  • Alex is correct. In addition, some storage engines (including InnoDB) are optimized for consecutive primary keys. Everything else in this answer is spot-on, though. – TehShrike May 21 '12 at 00:59
  • 1
    @TehShrike: You and Alex are both right, there is lots of software that requires or encourages a unique and sometimes monotonically increasing integer key for every "interesting" table. That doesn't change the fact that it's a duplicate key, and that it doesn't contribute anything to the data model. As Yogi Berra said, "In theory, there is no difference between theory and practice. But, in practice, there is." – Ross Patterson May 21 '12 at 11:35