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

Posted by Luc M on Stack Overflow See other posts from Stack Overflow or by Luc M
Published on 2010-04-05T18:18:46Z Indexed on 2010/04/07 21:03 UTC
Read the original article Hit count: 292

Filed under:

postgresql

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

Developer IT