How to handle product ratings in a database
- by Mel
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:
Allow users to execute an occasional write query to a table that will handle hundreds of read requests?
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,
