I'm hoping somebody here can give me some advice with this, but here goes.
I've got a jokes and humor website which I'm in the process of porting it over to a MySQL database. I'd like to include a feature whereby my users can rate the jokes and the ratings would be averaged out and displayed.
Now, my problem is that it sounds, to me at least, that this approach would be rather server taxing. I'd have to store every rating entered and average them out every time someone wanted to see the average rating.
Anybody have any other thoughts about how I could do this? I know this sort of thing has been done before, so if anybody has any thoughts, I'd really appreciate it.
Nothing comes to mind...server strain sounds inevitable for a site like this...perhaps you could have one database for the ratings and another that holds the average, or something along those lines, so you'd only have to access the one database, rather than calculate the average everytime...am I making sense?
I need to figure out a solution for this for my site as well. Using a seperate table for the average rating would require a field for the total number of votes as well, in order to allow the averaging. Most likely, you'd need to store the individual ratings as well, to show who rated what. It gets pretty complicated doing it this way.
What if you had your table with all the user ratings as well as a rating field in your jokes table. When somone submitted a rating, your site would calculate the new average rating for the joke and store it in the average rating field of your jokes table. When the jokes are displayed a precomputed rating would already be available.
That last post was a little unclear. Lets see if I can explain it better this time.
Lets say you have two tables:
with a "Joke Rating" field in addtion to your other fields.
with each individual rating.
Someone wants to rate your "Why did the chicken cross..." joke, so they fill in the form and submit. Now your program saves that rating in the ratings table. Then it caculates a new average rating for "Why did the chicken cross..." and stores it in the jokes table.
Whenever a user wants to view your jokes, you already have a rating for each one! No averageing a rating for each one for each viewing.
Since it computes this average rating every time a new rating is added, the average rating always remains current.
[This message has been edited by MAC (edited August 17, 2000).]