Ideas with application that filters relevant data

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Ideas with application that filters relevant data

Author

Topic

urki
Starting Member

1 Post

Posted - 2006-06-01 : 04:49:06

Hi,

I need some ideas how to make a special application.

I have a simple database with three tables:
Books
ID
Title

Members
ID
Name

Votes
ID
BookID (back to ID in table Books)
NameID (back to ID in table Members)
Vote (from 1 to 5)

The table Books has 6 000 titles, Members 3 000 and Votes has 50 000 votes about the books in a scale from 1 to 5.

People have their own opinion about books and sometimes a simple "AVG" isn't very relevant. With so much data I think it is possible to give the members personal recommendation and list those books he or she "should like".

Some members only rate 5 books, some rate 500 books. Sometimes everybody has the same opinion and sometimes it can be 50% "1" and 50% "5".

Can anybode give me some advice how to list those books he or she "should like" according to his/her votes in relation to others? I can't come up with a smart solution. Has anyone any ideas how to make this possible?

Thank you!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-06-01 : 06:56:17

This really isn't a SQL question. You need to come up with a method to answer the question about what someone should like, based on the data you have.

Google is your friend.

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-01 : 09:13:57

Use Bayesian! The formula for calculating the true Bayesian estimate.

weighted rating (WR) = (v ÷ (v+m)) × R + (m ÷ (v+m)) × C

R = average for the book (mean) = (Rating)
v = number of votes for the book = (votes)
m = minimum votes required to be listed
C = the mean vote across the whole report

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-06-01 : 09:42:15

quote:
Originally posted by Peso

Use Bayesian! The formula for calculating the true Bayesian estimate.

weighted rating (WR) = (v ÷ (v+m)) × R + (m ÷ (v+m)) × C

R = average for the book (mean) = (Rating)
v = number of votes for the book = (votes)
m = minimum votes required to be listed
C = the mean vote across the whole report

He is looking for something to decide if an individual would like the book, so I don't see how it would work for this.

CODO ERGO SUM

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-06-01 : 17:24:29

What about this:

DECLARE @MemberID int
SET @MemberID = 1

SELECT B.Title, GB.Score
FROM ( -- Books liked by other members with similar taste
   SELECT V0.BookID, AVG(V0.Vote) AS Score
   FROM Votes V0
   WHERE V0.NameID IN ( -- Members with similar taste
      SELECT DISTINCT V1.NameID
      FROM Votes V1
      WHERE V1.BookID IN ( -- Books this member likes
         SELECT V2.BookID
         FROM Votes V2
         WHERE V2.NameID = @MemberID AND V2.Vote > 3) AND
			   V1.Vote > 3 AND V1.NameID <> @MemberID) AND
      V0.BookID NOT IN ( -- Skip books already read
         SELECT V3.BookID
         FROM Votes V3
         WHERE V3.NameID = @MemberID)
   GROUP BY V0.BookID
   HAVING AVG(V0.Vote) > 3) AS GB
INNER JOIN Books B
ON B.ID = GB.BookID

it will give a list of good books (here defined as being rated better than 3) that the member hasnt already read (or rather voted on) averaged from votes from members who have liked (again by vote > 3) at least one book that the member has rated good (and yes here > 3 is used again).

-- This one's tricky. You have to use calculus and imaginary numbers for this. You know, eleventeen, thirty-twelve and all those.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources