On 5 Oct 2009, at 11:17pm, P Kishor wrote: > I am trying to develop a weighted rating system. I have the > following schema -- > > CREATE TABLE ideas ( > idea_id INTEGER PRIMARY KEY, > idea_stars REAL DEFAULT 10, -- creator's 10 out of 10 stars; > fractional possible > idea_votes INTEGER DEFAULT 1 -- creator's vote > ); > > Every item above, upon creation, gets 10 out 10 possible stars from > its creator, and 1 vote of its creator. The creator's vote is stored > in the following table -- > > CREATE TABLE votes ( > user_id INTEGER, > idea_id INTEGER, > num_of_stars INTEGER, > PRIMARY KEY (user_id, idea_id) > );
I assume that either the same operation creates records in both TABLEs, or you have a trigger on TABLE ideas which creates a record in TABLE votes. From the above PRIMARY KEY, one person can cast zero or one vote on each idea. If the same person votes on the same idea again, their new vote supersedes the previous vote. If those are wrong, reconsider your PRIMARY KEY. > Note: user_id is from a users table not shown here. > > Everytime a user rates an 'idea', the following happens > > INSERT INTO votes (:user_id, :idea_id, :num_of_stars); > > Now, I want to fire a trigger that calculates a weighted rating for > the item just inserted into 'votes' for every subsequent user who > comes by and votes. The cumulative rating of every entry in 'ideas' > (stored in ideas.idea_stars) should be weighted by the number of votes > cast for that entry (stored in ideas.idea_votes). I believe this would > be a Bayesian ranking, no? No. Your idea is fine and useful and will work: just divide the total number of stars by the total number of votes and you get an average of the votes. Bayesian statistics is about starting off with a statement (It will rain on the 24th of July.) and using little pieces of independent information to judge whether this statement is true or not. Your problem here is that your statement is dependent on the little pieces of information: you're going to use the average vote to decide whether the idea should be adopted. It's a self-forfilling prophesy. If you always use the votes to decide whether the idea should be adopted, your Bayesian ranking is 1: perfect. > CREATE TRIGGER update_ideas_votes AFTER INSERT ON stars > BEGIN > UPDATE ideas > SET > idea_stars = ??, > idea_votes = idea_votes + 1 > WHERE idea_id = new.idea_id; > END; > > I have been reading a bit about Bayesian ranking, but ?? above has me > lost for words. So, I come to you. Your '??' should be 'idea_stars + new.num_of_stars'. Makes perfect sense. But don't forget other TRIGGERs. What happens if a vote changes, or is deleted ? Can an originator withdraw an idea ? Simon. _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
