Ok, restating the original problem based on the immediate feedback from Igor and Simon (original text of the email at the very bottom, but now superseded by what follows)
I am trying to develop a weighted rating system for "ideas." People propose "ideas" and other folks rank them. The highest rank ideas bubble up to the top (or bottom, as one may desire). The rank of an "idea" is a function of the rank given to it by each person who voted for it and the number of people who voted for it. Basically, I want to imply more confidence in the calculated rank for an "idea" if the number of people who voted for it is higher... so, more the number of votes for an "idea," more the confidence in its rank. I have the following db schema in which to store these, but please feel free to suggest improvements/modifications The following table stores the ideas, their calculated rank and the number of votes garnered per idea CREATE TABLE ideas ( idea_id INTEGER PRIMARY KEY, idea_desc TEXT, idea_stars REAL, -- calculated rank 1-10; fractional possible idea_votes INTEGER DEFAULT 1 -- total votes cast; creator's vote is the default ); Each user'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. Every time a user creates an 'idea', the following happens -- (1a) INSERT INTO ideas (idea_desc, idea_stars) VALUES (:idea_desc, :calculated_stars); (1b) INSERT INTO votes (:user_id, :idea_id_from_(1a)_above, 10); The logic behind giving 10 stars to the idea from its creator is that the creator obviously thinks very highly of the idea. Besides, it provides a seed ranking to the idea although that ranking would be very low based on our magic formula (the one that returns :calculated_stars in 1a above) because it is based on only one vote. Every time a user votes for an existing 'idea', the following happens -- (2a) INSERT INTO votes (:user_id, :idea_id, :num_of_stars); (2b) UPDATE ideas SET idea_stars = :calculated_stars, idea_votes = idea_votes + 1 WHERE idea_id = :idea_id_from_(2a)_above; I read a couple of articles on weighted rankings, including the one at http://www.thebroth.com/blog/118/bayesian-rating Here are the conditions -- No, an idea, once suggested, cannot be deleted/withdrawn. Of course, it can get so few votes or so low votes (or combination thereof) that it falls off the priority list. Yes, a particular vote can be changed, but of course, only by its voter. So, if I gave 10 stars to an idea today, I can come back tomorrow and change it to 8 stars. No, no one can vote more than once for an idea, but of course, not everyone is required to vote for every idea. Many thanks for your consideration. On Mon, Oct 5, 2009 at 5:17 PM, P Kishor <[email protected]> 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) > ); > > 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
