Search code examples
sqlrostering

SQL database sports league statistics rosters

I am making a DB to store basketball stats. I am trying to determine if my approach for keeping track of the rosters is sensible.

Over the seasons, players may change teams or leave/join the league entirely. I want to keep track of this and maintain the ability to query:

  • A player's statistics broken out by his tenures on each team
  • Team statistics (which are mostly derived from their players) for any period of time such as a season including all the games any player played for the team.
  • The team rosters at any date

I have an approach but I'm having a bit of difficulty thinking about its potential limitations in the future.

  • The table playerStats has each player's statistics for a game, with a gameID, teamID.
  • The games table has the date for each gameID.
  • The rosters table to have columns for playerID, teamID, a fromDate and a toDate.

Would this be sufficient? Do I even need the rosters table, otherwise do I need the teamID in the playerStats table or is it redundant to have both? Is this generally the approach for a roster-type of DB.

Thank you!

Solution 
  • game
----------
game_id (pk)
team_1_id
team_2_id
play_date
location_id

roster
----------
player_id
team_id
begin_date
end_date

game_stats
-------------
game_id
player_id
stats...