Reputation Formula - Best Approach

I have a table in Oracle that records events for a user. This user may have many events. From these events I am calculating a reputation with a formula. My question is, what is this best approach to do this in calculating and returning the data. Using a view and using SQL, doing it in code by grabbing all the events and calculating it (problem with this is when you have a list of users and need to calculate the reputation for all), or something else. Like to hear your thoughts.

Comments * (.1) + 
Blog Posts * (.3) + 
Blog Posts Ratings * (.1) + 
Followers * (.1) + 
Following * (.1) + 
Badges * (.2) + 
Connections * (.1) 
= 100%

One Example

Comments:

This parameter is based on the average comments per post.

•   Max: 20
•   Formula: AVE(#) / max * 100 = 100%
•   Example: 5 /10 * 100 = 50%

Max is that maximum number to get all that percentage. Hope that makes some sense.

We are calculating visitation, so all unique visits / date of membership is another. The table contains an event name, some meta data, and it is tied to that user. Reputation just uses those events to formulate a reputation based on 100% as the highest.

85% reputation - Joe AuthorUser been a member for 3 years. He has:
•   written 18 blog posts 
o   2 in the past month
•   commented an average of 115 times per month
•   3,000 followers
•   following 2,000 people
•   received an开发者_开发技巧 average like rating of 325 per post 
•   he's earned, over the past 3 years: 
o   100 level 1 badges
o   50 level 2 badges
•   he's connected his: 
o   FB account
o   Twitter account

As a general approach I would be using PL/SQL. One package with several get_rep functions.

function calc_rep (i_comments in number, i_posts in number, i_ratings in number,
                  i_followers in number, i_following in number, i_badges in number,
                  i_connections in number) return number deterministic is
...
end calc_rep;

function get_rep_for_user (i_user_id in number) is
  v_comments ....
begin
  select .....
  calc_rep (v_comments...)
end get_rep_for_user;

If you've got to recalculate rep for a lot of users a lot of the time, I'd look into parallel pipelined functions (which should be a separate question). The CALC_REP is deterministic as anyone with the same set of numbers will get the same result.

If the number of comments etc is stored in a single record, then it will be simple to call. If the details need to be summarised up, then use materialized views for the summaries. If they need to be gathered from multiple places, then a view can be used to encapsulate the joins.

Whether you can calculate on the fly fast enough to meet requirements is a factor of data volumes, database design, final calculation complexity..... to imagine that we can give you a cut-and-dry approach is unreasonable.

It may wind up being something that would be helped by storing summaries used for some calculated values. For example, look at the things that cause DML. If you had a user_reputation table, then a trigger on your blog_post table could increment/decrement a counter on user_reputation on insert or delete of a post. Same for comments, likes, follows, etc.

If you keep all of your summaries up to date in that manner, then the incremental costs to DML will be minor and the calculations will become simple.

Not saying that this is THE solution. Just saying that it might be worth exploring.