In this article I’m going to explain how to build a 5 star rating system with MySQL. We will be using triggers to make the queries lightweight and fast. Triggers are supported in MySQL since version 5.0.2 so make sure you’re up to date. I will not explain how to implement the results in PHP, Ruby or whatever floats your boat. You can find plenty of tutorials on the web about that so no need to write another article. This article is based on Evan Miller’s tutorial ‘How Not To Sort By Average Rating‘.
The Problem
There are a lot of rating systems available and many of them are wrong. The majority of rating tutorials you’ll find on the web take the average rating, without taking the total number of votes into account. But it won’t stop there. Average rating systems can be found anywhere on the web. Even on big websites like Amazon. So why is it wrong?
Suppose you have 10 products on your website. Product 1 has a total of 10 votes, where 9 are positive and 1 is negative. With an average five star rating system that rating would be (9 / 10) * 5 = 4.5 stars. Now you have another product with 1 vote, which is positive. That would mean the product would have (1 / 1) * 5 = stars. So product 2 would rank higher than product 1, even though product 1 has 9 positive votes and only 1 negative vote.
The Solution
The solution to this problem is normal approximation interval. Wikipedia tells us:
“The most commonly used formula for a binomial confidence interval relies on approximating the distribution of error about a binomially-distributed observation, with a normal distribution
