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)
);
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?
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.
--
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
-----------------------------------------------------------------------
Assertions are politics; backing up assertions with evidence is science
=======================================================================
Sent from Madison, WI, United States
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
