How to handle product ratings in a database

Posted by Mel on Stack Overflow See other posts from Stack Overflow or by Mel
Published on 2010-05-23T17:29:06Z Indexed on 2010/05/23 17:40 UTC
Read the original article Hit count: 151

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,

© Stack Overflow or respective owner

Related posts about mysql

Related posts about database-design