whats a good sql example on how to make a good rating system?

__________________
VillageIdiot can have my babbies ;d

Why do people avoid my topics.. this is sad..

__________________
VillageIdiot can have my babbies ;d

Could you give us an example, perhaps? A little more detail would be lovely. What do you have at the moment? Have you made an attempt at it?

__________________
The man who comes back through the Door in the Wall will never be quite the same as the man who went out.

What type of rating system? A five star style? Ebay style?

We are also more compelled to help when the person asking the question shows that they cared enough to work on it. Please show us any work/thought you have given to it so far.

__________________

What is Ebay Style?

__________________
VillageIdiot can have my babbies ;d

I've tried a thousand times, the mysql code doesn't like MAX / AVG, so that way I can find out which rating is used more out of 1 2 3 4 5

__________________
VillageIdiot can have my babbies ;d

So.. you don't want to post what you've done so far?

__________________

Since i don't really have something "so far". I made this example. I hope this is a good example. ;P


The problem is, if I have an average of for example 4 "5" ratings, while as theres 2 "1" ratings.. It usually returns the 1 rating. But you have more "5" ratings.

__________________
VillageIdiot can have my babbies ;d

Just .. lol hehe xD
Take (4*5) + (2*1) + (if you have 3 "2" ratings= 3*2, etc.)/ (4+2+3) <- the total amount of ppl who have rated

Then you'll get an avarage of say 4,3 or w.e it is(didn't check). Then you just round up(which in this case will round down to 4).
Get it?

__________________

Hmm, well if formulas are required, why not just do baysien estimates? but yes i do get it, thank you. ;D

__________________
VillageIdiot can have my babbies ;d

Yah :) I have no idea what the estimates was.
But the basic idea here is that you have a table "ratings".

id, productid(or w.e id you have on the thing that is being rated), 1, 2, 3, 4, 5

Then when someone rates 2, add one to the "2" column
And when getting the actual rating of the product(or w.e), you do the formula that I posted in my previous post

__________________

For a rating system like that, use this code to get an ordered ranking list:
To get a particular user:
This assumes a databse of:
int userID (The ID of the person being rated)
float rating (The rating given to the person)

Note: The code was tested in MSSQL, not MySQL, but it should work.

__________________

@up- So.. how do you add rating to the user? If I rate someone as 3 of 5, then what will be inserted into db?

And also, how do you count how many ppl have rated?

__________________

1. How do you add rating to the user
Insert a row with the user ID being the ID of the user being rated and the rating as an integer ranging from 1-5. Individual database structures will vary, but that is the basic idea.

2. If I rate someone as 3 of 5, then what will be inserted into db?
A value of 3 and the ID of the user

3. And also, how do you count how many ppl have rated?
How many people have submitted a rating? You would have to add a column that contains the ID of the person who rated the person and write a query to count the distinct user IDs.

__________________

Hm, ye okey. Your idea is good, but say you have 1000 users, and everyone rates everyone, then you'd have 1000*1000 entries in your ratings db, which is.. quite alot. If you would do it my way, you would just have 1000 entries in the db, and all the ratings could be counted quite easy, while it's really simple to calculate the avarage ratings that someone has got

__________________

I tried this, and I got 23.

__________________
VillageIdiot can have my babbies ;d

That cannot be correct.

4*5 = 20
2*1 = 2
3*2 = 6

20+2+6 = 28

4+2+3 = 9

28 / 9 = 2,9(around.. 27 / 9 = 3, so 28 / 9 should be slightly less than 3).

Final algorythm:
(4*5) + (2*1) + (3*2) / (4+2+3)

__________________

It would only be a million rows, which would not be terrible if you had that much traffic. But you would have to update ratings every so often to a smaller table and stuff like that. There would be so simple way to make a script for a site like that. I'm not going to get into large database stuff right now since I don't have a ton of experience with it.

While you could not keep records of who rated who (meaning one person could rate someone to whatever they wanted), you could have it keep adding the numbers (a 5 and a 2 would make the value 7) and divide it by the number of ratings given.

So you would have on your users table:
ID
rating
rater_count

Then keep adding rating by each number given and increment rater_count by one. Dividing the two would give you the average.

__________________

I just copied it onto a variable, and outputted it. :P gave me 23

Edit:
Okay, PHP can't do math well, I did it in the calculator, and I got 3.1

__________________
VillageIdiot can have my babbies ;d

@village- Hm, yea.. well. There's probably TONS of ways of doing a rating system

@orc- Heh, ohye, stupid me.. slightly MORE than 3 ofc, not slightly less >.<
But it's weird that PHP can't do that math :S

__________________