Take the 2-minute tour ×

I am creating a review site where certain details of a product is determined from the aggregated responses of users who have written review for a particular product. For example, when a user is reviewing the product Macbook Air, apart from rating it 1-5 stars and writing a 300 word description of his experience using the laptop, he can also do a short 'survey' containing checkboxes where he can choose if the product is recommended for:

  1. office suite
  2. games
  3. graphic design
  4. watching movies

For example, the user can select the checkbox for 'office suite' and 'watching movies'. Assume the case where all the responses of all the reviewers for this Macbook Air product results in 100 votes for 'office suite' and 50 votes, 20 votes and 10 votes for the other options. Because 'office suite' option have the most votes, on the product page of the Macbook Air, it will state:

Product recommended for: Office Suite

How will you go about designing the database for this? I am thinking of having a separate table with the columns 'rec_office_suite', 'rec_games', 'rec_graphic_design', 'rec_watching_movies' each containing the number of votes for that option. Every time a reviewer submits his review and fills up the mini survey, the database table will be updated with the fields he has chosen to be incremented +1.

The thing is that this can end up with a table with many fields. Will this be a problem?

share|improve this question

2 Answers 2

up vote 1 down vote accepted

I'd recommend a combination of your proposed solution in the question and Nobita's method.

Nobita is correct in that using linked tables is the correct way of storing the data. It's great for later expansion (simply add a new category record). I'd start this way.

When it comes to displaying the results on each product page though, it will be quite demanding on the database to count all votes for all categories for each product. So it makes sense to duplicate the data in a sort of cache for fast reading. Your idea of a table column for each category isn't easy to expand later though and could get very large. Instead I'd simply store the votes in a MEDIUMTEXT (or similar) column on the product table - perhaps in JSON format or a serialized PHP array - which is very simple to read. The cache only needs updating whenever someone adds a category vote, or when a new category is added.

By using both you have easy access to product/category/vote data for more complex SQL queries later on, and fast queries for product pages.

Hope that makes sense, half asleep :)

share|improve this answer

I would do the following, but let's see what people recommend:

  1. Create a table that has three fields: Product_ID - Category_ID - Votes
  2. Every time a user votes for that category in that product increment vote field in the above table.

This design will help you to have categories related to many products because you have an intermediate table that will deal with votes related to that category-product.

I hope this helps.

share|improve this answer

Your Answer


