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
