I'm trying to wrap my head around the proper design to calculate an average for multiple items, in my case beers. Users of the website can review various beers and all beers are given a rating (avg of all reviews for that beer) based on those reviews. Each beer review has 5 criteria that it's rated on, and those criteria are weighted and then calculated into an overall rating for that particular review (by that user).
Here are some of the relevant models as they currently stand. My current thinking is that all beer reviews will be in their own table like you see below.
class Beer(models.Model):
name = models.CharField(max_length=200)
brewer = models.ForeignKey(Brewery)
style = models.ForeignKey(Style)
.....
class Beerrating(models.Model):
thebeer = models.ForeignKey(Beer)
theuser = models.ForeignKey(User)
beerstyle = models.ForeignKey(Style)
criteria1 = models.IntegerField
...
criteria5 = models.IntegerField
overallrating = models.DecimalField
My real question is how do I calculate the overall beer average based on all the reviews for that beer? Do I keep a running tally in the Beer model (e.g. # reviews and total points; which gets updated after every review) or do I just calculate the avg on the fly? Is my current db design way off the mark?
I'll also be calculating a top beer list (100 highest rated beers), so that's another calculation I'll be doing with the ratings.
Any help is much appreciated. This is my first web app so please forgive my noob-ness. I haven't chosen a DB yet, so if MYSQL or PostgresSQL is better in some way over the other, please provide your preference and perhaps why if you have time. I'll be choosing between those two DB's. I'm also using Django. Thank You.