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,