A key functionality of my website is the review/rating system - people can either rate an item, in which case I store the rating, date, and ip address (to prevent repeated submissions), or they can write a review, in which case I also store the review text and author. I have a working system, but there are a few things about it I think should be improved. I'm asking here because I suspect that the ability to come up with a more efficient system goes hand in hand with php/mysql knowledge that I do not have.
First off, here's my current solution. Each item's database entry includes two fields for the rating process, num_ratings and sum_ratings. In addition to this, there is a separate table of reviews, with a field specifying which item each review belongs to. Every time an item is rated or reviewed num_ratings is incremented by 1, the rating is added to sum_ratings, and a row is added to the reviews table.
There are a few things I like about it:
- avg rating and the number of ratings are easily obtainable, and therefore can be used on pages that display this information for many items
- every rating is stored, leaving open the possibilities for more advanced rating display or analysis and gathering more information for every rating
But here's what worries me:
- The same information is stored in two places, introducing the possibility of getting out of sync, increasing processing time and memory usage, etc
- Reviews are not efficiently accessible. In order to distinguish them from ratings, I need to test the comments field of the review entry. This means that I need to loop through all the reviews to count or display them.
- One of the most accessed numbers, the average rating, requires a calculation, albeit a short one.
My goal is to make this viable for even very large databases. Does anybody have a better method? It there a standardized method? I don't want a plugin, but I would be glad to study a snippet of code.
Thanks for reading.
