Best practice for calculate user rate and more

I am building an application that shares some stuff...

Each Object can be rated has a rating 1..5 start. I keep for each the number of rates per star so can calculate the Avg rate.

So per Obj I have: Avg rate and total rate. I need to get the top10 rated Obj - so can do it using AvgRate+TotalRate (those who has these values as top10).

I want to have in the server an sql table like this: ObjId (index), totalRate, AvgRate... If possible to have this table sorted so that can get the top10 as the first 10? How can query the top10 with the calculation I want?

Also - I need to get the top10 per users. So per user I have all the Obj he shared so can have all of the rates of these Obj - with all info per Obj as mentioned before.

I need to know how to calculate a user rate, and also - how to fast get the top10.

Any ideas?

Solution

Later Edit: Sorry, didn't understand your question when writing this answer, gonna leave it still for others..

What's your formula for TotalRate ? And what do you mean by "so can do it using AvgRate+TotalRate" Why are you summing an average to TotalRate - whatever that is?

Best practice is to always compute the sums/averages incrementally. I would model Obj like this:

A total number of rates received
B total sum of points received
C average (float: B/A )
D - foreign key to user (author/owner of Obj)

When object receives rate X, you then recompute A = A + 1, B = B + X, C = B/A. In the same manner pre-compute aggregate sums/average. So if Obj belongs to user, create the same fields (A, B, C) to User model/table, and when Obj receives rate X, also update A, B, C values for user D (owner of Obj). Then, when selecting top 10 users, you do not need to join with Obj table (which may get huge), you only select users - descending by B or C column, limit 10.