CODEWITHSUNDEEP
phpmysqlrating-system
hieimora
hieimora

Reputation: 313

Bayesian Rating set up

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.

  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

Upvotes: 3

Views: 394

Answers (1)

Jonathan Vanasco
Jonathan Vanasco

Reputation: 15690

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.

Upvotes: 1

Related Questions