贝叶斯评级设置

Question

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

Answer 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.