keeps21 Posted May 21, 2009 Share Posted May 21, 2009 I have a website for a hockey team. Part of the website is to show stats for the current and past seasons. At the minute I have a table (players) that contains: playerid (PK) forename surname position I have another table (stats) that contains stats_id (PK) gamesplayed goals assists If I was to just keep the player info and stats for one season then I could add player_id to the stats table and use it as a foreign key. However I want to be able to keep stats for players for this season, last season, the season before etc. and want to eliminate data redundancy - ie i don't want to a new row for an existing same player for each season So I need to link the stats table and players table with the season. Would I just create a table containing season_id (PK) season eg 2007-08 player_id (FK) stats_id (FK) Any advice would be greatly appreciated. Quote Link to comment Share on other sites More sharing options...
kickstart Posted May 21, 2009 Share Posted May 21, 2009 Hi Not sure how much people move around. For example your suggestion would possibly fall down if a players moved from one position to another mid season and you kept them recorded seperatly (ie, in each team). All the best Keith Quote Link to comment Share on other sites More sharing options...
keeps21 Posted May 21, 2009 Author Share Posted May 21, 2009 Really in this case the position isn't important that important - all the stats kept are the same for all positions. Even if a player was to change position the stats would just continue to accumulate - they wouldn't be reset or recorded differently for a new position. Quote Link to comment Share on other sites More sharing options...
keeps21 Posted May 21, 2009 Author Share Posted May 21, 2009 If I was storing stats for multiple seasons for each player then there'd be no problem. The bit that's confusing me is that i want to store stats for players who may have left the club so need a way to reference the season that they played when showing a team roster. I guess I could perform a join on the players and stats table and just show players who have stats for the year I want to show, but that doesn't seem like good practice or am I trying to make it more complicated than it needs to be? Sorry if i'm not making much sense. Quote Link to comment Share on other sites More sharing options...
Hybride Posted May 21, 2009 Share Posted May 21, 2009 Would adding a "season" and "inTeam" (name of team/quit?) to the data table help? "Season" would reference to the season you are looking up, and "inTeam" would reference to whether or not they are in the team (team name or no, if not in team anymore.) Sorry, somewhat confusing what you're asking for, but I hope that helps. Quote Link to comment Share on other sites More sharing options...
kickstart Posted May 21, 2009 Share Posted May 21, 2009 Hi In which case I would say the join you suggest is perfectly valid. What you propose is fine for storing a limited amount of data. If you want to break it down further (eg, store the position a player was in each match, which matches they scored in, which match was against which opposition, which season a match was in, etc) then it would make sense to split the data up further. All the best Keith Quote Link to comment Share on other sites More sharing options...
keeps21 Posted May 21, 2009 Author Share Posted May 21, 2009 Hi In which case I would say the join you suggest is perfectly valid. What you propose is fine for storing a limited amount of data. If you want to break it down further (eg, store the position a player was in each match, which matches they scored in, which match was against which opposition, which season a match was in, etc) then it would make sense to split the data up further. All the best Keith Thanks for your input guys. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.