3

I'm trying to figure how I would start setting up the Bayesian rating system in MYSQL. I am not using a 5 star rating, but something similar. Mine will be a 5 volume bar.

This is for my album page. Instead of users rating the album, they will rate the songs that will be averaged to become the album rating.

  • $songsum
  • $songavg
  • $numofrated
  • $totalsongs
  • $albumrating
  1. $songsum / $numofrated = $songavg
  2. $songavg * ($numofrated / $totalsongs) = $albumrating

The another page (artist page) will also be rated.

$avg_num_votes  = 18;  // Average number of votes in all products
$avg_rating     = 3.7; // Average rating for all products
$this_num_votes = 6;   // Number of votes for this product
$this_rating    = 4;   // Rating for this product

$bayesian_rating =
    ( ($avg_num_votes * $avg_rating) + ($this_num_votes * this_rating) )
  / ($avg_num_votes + $this_num_votes);

How should I start setting this up? Should $this_rating be in the same table as the album?

Additional information
  • Tables in MYSQL: Albums, Songs, Genres, Solo, Group
  • Already has foreign keys linkage
hieimora
  • 313
  • 1
  • 4
  • 13
  • 1
    Your question does not contain enough information to answer it precisely (IMHO). However from the information you give with your question, I'd say that at least you should place the rating related data into the *same* database. – hakre May 24 '12 at 21:28
  • Hmm. What else do you need to know? And thanks for the feedback. – hieimora May 24 '12 at 21:40
  • it seems like this may be considered a duplicate question: http://stackoverflow.com/questions/6653215/bayesian-rating – Ryan May 24 '12 at 22:27
  • I saw it on another website as a example. I thought the same thing since I saw yours first. It is a good example. – hieimora May 24 '12 at 22:31
  • Yeah for sure. It was a good example. I didn't realize it was a different question. I'll delete my comment. This is a good question. +1 – Ryan May 24 '12 at 22:32

1 Answers1

1

your initial ideas for a rating scheme are likely to change ( especially for the album ), so you're best off to store transactional information which will let you recompute everything whenever you want.

create a new table called "Ratings" that fkeys on "Songs" and "Users":

  • id
  • song_id
  • (album_id)
  • user_id
  • rating

album_id isn't necessary since you have the song_id, but it'll save some time and disk-space is cheap

add the following fields to the database:

  • song.summary_votes
  • song.summary_average
  • album.summary_votes
  • album.summary_average

periodically run a script that updates the summary_ fields to whatever you want them to be.

Jonathan Vanasco
  • 15,111
  • 10
  • 48
  • 72