Star Rating in php with mysql

Question

I am working on 5 star rating which is working fine. Now I want to optimize the database.

CREATE TABLE IF NOT EXISTS `rating` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `article_id` int(11) NOT NULL,
  `vote` float NOT NULL,
  'user_id' int(11) NOT NULL

  PRIMARY KEY (`id`)
)

This is my current database. Now if 10,000 user rate the same article, I got 10000 records in a single table.

Can anybody helps me to understand and optimize the database?

`10000` records are considered as small amount, now the optimization depends on the nature of the operation you perform on the application and the query which is/are often used. You need to provide those details first. — Abhik Chakraborty, Oct 24 '16 at 06:45
A column for each star. 1*, 2* and so on. Just add the number everytime people rate it. — Irvin, Oct 24 '16 at 06:46
@AbhikChakraborty it can we 10 lakh as well for one article ,, if i got 100 aricle then 100 * 10 lakh will be large — Ankita Kashyap, Oct 24 '16 at 06:49

score 1 · Answer 1 · edited May 23 '17 at 12:19

This depends on what your rating algorithm and requirements are. You can just record the number of stars received using five fields instead of one:

star1    integer not null default 0,
star2    integer not null default 0,
...
star5    integer not null default 0,

but if you do, you lose the possibility of "cooling off" ratings and changing idea on the user's part.

On the other hand, this accepted answer algorithm requires only to store the mean, so you can store

stars    integer not null default 0,
voters   integer not null default 0,

...but now the user cannot retract his vote, and indeed not even know whether he has voted or not.

(You can compromise by storing daily or weekly votes into one table, with datetime and voter id and whatever, and then "distilling" older results in the average table. Now the voter can see and undo what he did in the last week).

So what you need to do is to clearly state your requirements - what can the user do? What must the system be able to do? etc. - and work out what information needs to be known; then you derive the table(s) structure from there. If the data is too much (10,000,000 records isn't much) you either try throwing more hardware at it, or see if you can scale somehow - for example if you don't (often) need cross-article information (such as "the most starred articles"), nothing stops you from partitioning the rating table across different servers based on article id until each partition is small enough for your taste.

Star Rating in php with mysql

1 Answers1