How do I model product ratings in the database?

Question

What is the best approach to storing product ratings in a database? I have in mind the following two (simplified, and assuming a MySQL db) scenarios:

Create two columns in the products table to store the number and the sum of all votes respectively. Use the columns to get an average at run time or using a query.

This approach means I only need to access one table, simplifying things.

Normalize the data by creating an additional table to store the ratings.

This isolates the ratings data into a separate table, leaving the products table to furnish data on available products. Although it would require a join or a separate query for ratings.

Which approach is best, normalised or denormalised?

Answer 1

A different table for ratings is highly recommended to keep things dynamic. Don't worry about hundreds (or thousands or tens of thousands) of entries, that's all peanuts for databases.

Suggestion:

table products

• id
• name
• etc

table products_ratings

• id
• productId
• rating
• date (if needed)
• ip (if needed, e.g. to prevent double rating)
• etc

Retrieve all ratings for product 1234:

SELECT pr.rating FROM products_ratings pr INNER JOIN products p   ON pr.productId = p.id   AND p.id = 1234

Average rating for product 1234:

SELECT AVG(pr.rating) AS rating_average -- or ROUND(AVG(pr.rating)) FROM products_ratings pr INNER JOIN products p   ON pr.productId = p.id   AND p.id = 1234;

And it's just as easy to get a list of products along with their average rating:

SELECT   p.id, p.name, p.etc,   AVG(pr.rating) AS rating_average FROM products p INNER JOIN products_ratings pr   ON pr.productId = p.id WHERE p.id > 10 AND p.id < 20 -- or whatever GROUP BY p.id, p.name, p.etc; 

Answer 2

I know that my answer is not what you actually ask for, but you might want to have a chance of facilitating that new products with your system can almost never beat the old products. Say that you would get a product with 99% rating. It would be very difficult for new products to get high if you sort by products with the highest rating.