How Implement a system to determine if a milestone has been reached

Question

I have a table named stats

player_id team_id match_date  goal assist`  
        1       8  2010-01-01    1      1
        1       8  2010-01-01    2      0
        1       9  2010-01-01    0      5
  ...

I would like to know when a player reach a milestone (eg 100 goals, 100 assists, 500 goals...)
I would like to know also when a team reach a milestone.
I want to know which player or team reach 100 goals first, second, third...

I thought to use triggers with tables to accumulate the totals.
Table player_accumulator (and team_accumulator) table would be

player_id total_goals total_assists
        1           3             6


team_id   total_goals total_assists
      8             3             1
      9             0             5 

Each time a row is inserted in stats table, a trigger will insert/update player_accumulator and team_accumulator tables.
This trigger could also verify if player or team has reached a milestone in milestone table containing numbers

milestone
      100
      500
     1000
      ...

A table player_milestone would contains milestone reached by player:

player_id  stat    milestone          date
        1  goal          100    2013-04-02
        1  assist        100    2012-11-19

---

There is a better way to implements a "milestone" ?
There is an easiest way without triggers ?

I'm using PostgreSQL

Answer 1

I'd just count all goals and assists of a player which scores, and team, which scores.

Like this on client side (in pseudocode):

function insert_stat(player_id, team_id, match_date, goals, assists)
{
  if (goals>0) {
    player_goals_before = query('select count(goal) from stats where player_id=?',player_id);
    team_goals_before = query('select count(goal) from stats where team_id=?',team_id);
  }
  if (assists>0) {
    player_assists_before = query('select count(assist) from stats where player_id=?',player_id);
    team_assists_before = query('select count(assist) from stats where team_id=?',team_id);
  }
  query("insert into stats (player_id, team_id, match_date, goal, assist)\n"
    +"values (?, ?, ?, ?, ?)", player_id, team_id, match_date, goal, assist);

  if (goals>0) {
    if ( has_milestone(player_goals_before+goals) and !has_milestone(player_goals_before) ) {
      alert("player " + player_id + " reached milestone!")
    }
    if ( has_milestone(team_goals_before+goals) and !has_milestone(team_goals_before) ) {
      alert("team " + team_id + " reached milestone!")
    }
  }
  // etc
}

Do not maintain milestone table, as this makes the database denormalized. I think this is a premature optimization. Only when the above is really not fast enough (for example when stats will have more than few thousands of rows per player_id or team_id) then you can think of maintaining milestone table.