-1

I need to set up a simple voting system for my application., My application consists of articles posted as well as comments. I would like to add voting abilities to both articles and comments and at the same time be able to sort comments based upon highest voted etc.

I have the following restrictions i.e since the application needs users to log in - only logged in users can vote, secondly a user can vote on an item only once. Users can upvote or downvote or cancel a vote they've made.

What would be a decent table design for this, plus I need the solution to be scaleable. Thanks for the advice

Ali
  • 7,353
  • 20
  • 103
  • 161
  • You can get an idea in this tutorial, just adopt it to your application' [link](http://net.tutsplus.com/tutorials/javascript-ajax/creating-a-dynamic-poll-with-jquery-and-php/) – Rho Mar 26 '11 at 12:36

1 Answers1

2

I think I would go with a join-table between the users and articles tables :

users_articles
  - article_id
  - user_id
  - score
  - date

With the following notes :

  • article_id is a foreign key to the article that gets up/down-voted
  • user_id is a foreign key to the user that voted
  • score is +1 or -1 depending on the vote
  • the primary key is on the two article_id, user_id columns.
  • a user voting on an article means inserting one line in this table ; canceling the vote means deleting that line (or setting a 0 score if you want to keep track of the fact the user has voted)


That's for votes on articles.
And I would do another users_comments table for the votes on comments.

Pascal MARTIN
  • 395,085
  • 80
  • 655
  • 663
  • Thats neat - I was thinking on using a single table for both with an extra field indicating the type of the element i.e if its an article or comment and another field to hold the id of the element but how would I be able to retrieve using SQL a set of lets say article titles against number of upvotes and downvotes or accumulated score – Ali Mar 26 '11 at 13:06