Proper rating system

[brendan6]

Im trying to install a product rating system based on the average of customer testimonials.  My question is whether it will be better to better to have an "average_rating" field in my database and maintain it for every new customer review or determine the average by querying for every review and getting the average on every page load...

[PHP_PhREEEk]

No need to store the average. Since averages are highly dynamic (well, depending on site traffic), just let PHP do it on the fly, per request. No harm in storing it if it makes you feel better, but there's no benefit either.

 

PhREEEk

[brendan6]

Say a search page brought up all the products and i wanted to sort them by average rating..do you think the volume of SQL queries to calculate that many averages would be slow/bog down the server?

[PHP_PhREEEk]

No matter how you search them, you'll have the same amount of queries, because you will need to load them into an array, then let PHP sort the array on whatever criteria you need. If you store that array into a SESSION var, then make copies of it whenever you need to do a custom sort or whatever, then in effect you have created a 'cache' and MySQL will not need to queried again (on a per-user basis). You have to 'think ahead' of your programming sometimes.

 

Anyways, as to this question:

> [- snip -]would be slow/bog down the server?

 

That question is -always- answered by what kind of machine is running the database, how many other large apps are sharing the resources, and most importantly, how much memory and the quality of that memory. MySQL likes memory. Lots of it. = )

 

PhREEEk