1

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!

allstar
  • 1,155
  • 4
  • 13
  • 29

2 Answers2

0

You do not really need Roster. You may want to have Team table (teamID, teamName) if you plan to have non-descriptive teamIDs and/or expect teams to change their names.

You will also need Players table to hold their names plus whatever other attributes you want to keep..

PM 77-1
  • 12,933
  • 21
  • 68
  • 111
  • I do have that. If I go without Roster, where will the information be stored as to what player is on what team? Would it just be determining what team the player played his last game for on any date? Is this preferable to removing the teamID from the playerStats table and keeping that info separate? – allstar Mar 04 '13 at 23:20
  • @allstar - If you want to know which players were on which team even if they were not actually playing then I guess something like `Roster` makes sense. – PM 77-1 Mar 04 '13 at 23:26
0
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...
Randy
  • 16,480
  • 1
  • 37
  • 55
  • So that's generally what I'm thinking. Do I even need to have team_1_ID and team 2 in the games? If the games occur, the player stats table would indicate what teams were playing via the roster table. – allstar Mar 04 '13 at 23:31
  • Maybe I'm trying too hard to avoid any redundancy? – allstar Mar 04 '13 at 23:32
  • i would do that - since you do not have a good way to know if any/every player on a team plays in a game - it would be easier to query even if that bit seems a bit redundant. Try to formulate the query without it and see if you like the SQL... – Randy Mar 05 '13 at 02:58