How to handle product ratings in a database

Question

Hello,

I would like to know 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:

Scenario 1: Create two columns in the product table to store number of votes and the sum of all votes. Use columns to get an average on the product display page:

products(productedID, productName, voteCount, voteSum)

Pros: I will only need to access one table, and thus execute one query to display product data and ratings. Cons: Write operations will be executed in a table whose original purpose is only to furnish product data.

Scenario 2: Create an additional table to store ratings.

products(productID, productName)
ratings(productID, voteCount, voteSum)

Pros: Isolate ratings into a separate table, leaving the products table to furnish data on available products. Cons: I will have to execute two separate queries on product page requests (one for data and another for ratings).

In terms of performance, which of the following two approaches is best:

1. Allow users to execute an occasional write query to a table that will handle hundreds of read requests?

2. Execute two queries at every product page, but isolate the write query into a separate table.

I'm a novice to database development, and often find myself struggling with simple questions such as these.

Many thanks,

Answer 1

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.

Answer 2

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