how to make mysql structure of up,down rating

Question

Every programmer here knows about ratings like that:

The problem is that I don't know how to make my SQL structure for that.

I think I can add up and down field in article table in MySQL, but this does not allow multi voting.

Could you please tell me how to make it?
Do I have to create a new table?

Answer 1

The easiest way is to simply store the vote counter per article.
When an article gets voted up, increase the counter. Voting down - decrease the counter.

If you need to keep track about which user voted up/down (and avoid multiple votes), you need to define an intersection table between users and articles.

It could look like this:

article_votes
--------------
user_id
article_id
vote

where vote can be either +1 or -1.

If you need the points of an article, you get it by

SELECT SUM( vote )
FROM article_votes
WHERE article_id = <your_article_id> 

Answer 2

You may get some ideas out of how stackoverflow does it:

• Stack Overflow Creative Commons Data Dump
• Understanding the StackOverflow Database Schema
• Meta Stackoverflow: Anatomy of a data dump