My database has the following (simplified) relationship…
comment -||-----0<- review
In the COMMENT table, is information about - you guessed it - the Commenter and the Comment.
In the REVIEW table, is information about how the Reviewer rated the Comment (e.g. Helpful (Y/N), Rating, etc.)
Currently, I have created some PHP Functions which calculate aggregate figures (“% Found Helpful”, “Avg Rating”, etc.) and display them. Unfortunately, these won’t help me with my next challenge…
I would like to expand Sorting to allow users to sort by things like “Most Helpful Comment” and “Avg Rating (Highest-to-Lowest)”.
As far as I can see, there are two approaches I could take…
Option #1: Add a Subquery to my existing Comment query which would calculate the Aggregate Values on-the-fly.
Problem is, this seems “expensive”.
Option #2: Every time a user reviews a Comment, I could calculate the new Aggregate Values and write them to my Comment table.
But this also seems “expensive” and it suffers from the fact that I would be storing Calculated Values in my Comments table which seems like a really poor choice.
There could be other ways to do this as well, but this is a problem which I have never encountered before.
Help and suggestions welcome!!