[SOLVED] How do I model product ratings in the database?

How do I model product ratings in the database?

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?

Solution

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 &lt; 20 -- or whatever
GROUP BY p.id, p.name, p.etc;