MySQL Table Structure For a Rating System

Question

I want to create a rating system that keeps track of who voted for which article, and how many votes that article received along with the value of each vote.

And I was wondering what is the best way to go about this?

Here is what I have so far below.

CREATE TABLE IF NOT EXISTS `vote` (
`counter` int(8) NOT NULL default '0',
`value` int(8) NOT NULL default '0'
)


CREATE TABLE articles (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`user_id` INT UNSIGNED NOT NULL,
`title` TEXT NOT NULL,
`summary` TEXT DEFAULT NULL,
`content` LONGTEXT NOT NULL,
PRIMARY KEY (id)
);


CREATE TABLE IF NOT EXISTS `users` (
`id` int(8) NOT NULL auto_increment,
`username` varchar(20) NOT NULL,
PRIMARY KEY  (`id`)
)

Answer 1

You need to keep track of your users somewhere

CREATE TABLE IF NOT EXISTS `vote` (
  `user_id` int(8) NOT NULL,
  `article_id` int(8) NOT NULL,
  `value` int(8) default 0,
  `datetime` timestamp DEFAULT CURRENT_TIMESTAMP
)

(Indexes are missing here and for the other tables)

If you want to unnormalize the number of votes, it should then be put in the articles table but if you do not have too much records, I would rather advice to compute it using a regular count and put the front in cache (refreshed each time there is a new vote for the article of course).