Database Newbie - Need Some "Get Up to Speed Quickly" Advice

[solid7]

I definitely care about which team the player played for. As Dal Jeanis stated, players can transfer mid-season, and I need to be able to capture that, also.

I had originally left the season in its own table, thinking that it would be better to separate it out for the sake of linking relationships later on...

[Dal Jeanis]

To me, it makes a whole lot more sense to (on a go-forward basis) track the individual games and then aggregate. It's the same amount of work to input, and then you have the ability to pull a whole lot more analysis out of the same data.

[solid7]

To me, it makes a whole lot more sense to (on a go-forward basis) track the individual games and then aggregate. It's the same amount of work to input, and then you have the ability to pull a whole lot more analysis out of the same data.

Well, not exactly. First off, there's that 10 years of data thing. That's a done deal. I know you said on a "go forward" basis, but it takes an entire team of people to track games and aggregate, and that's a luxury that I just don't have. (our format is established, and set in stone, and has since been automated) Even so, it's just not the data that I need. Yes, having too much data is better than having not enough. Except here. And now.

[Dal Jeanis]

Yeah, there's that pesky real-life installed base, getting in the way of my dreams of architectural perfection again... (some professionals would call them "hallucinations")...

Okay, so you are somewhat circumscribed by the way you have previously collected and collated the data, and by the way the data now comes to you.

Since I have no idea what format you have that data in presently, there's not a lot of useful advice I can give. Probably the best advice I can give is to pop over to MVP Roger Carlson's Access library at http://www.rogersaccesslibrary.com/ and review his tutorials about database design and application design. Then come back and ask more specific questions.

[June7]

Then add a field for the TeamID. This means a player could have multiple records for each season.

Can have a lookup table for the seasons but still need this value in the Stats table.

If you aren't keeping detail data at the game level, this really isn't all that complicated a data structure.

Someone else is maintaining per game stats and hands to you just aggregate data at the end of the season? So your db cannot substantiate the aggregate data, just have to have faith it is correct.

[solid7]

Someone else is maintaining per game stats and hands to you just aggregate data at the end of the season?

No, it is handed to me after every match. But it isn't tracking who played whom, and all the particulars surrounding that. We just want the players' numbers.

[June7]

Well then, exactly what is the detail level of this 10-years-worth of data? A record for each player for each match for each season and the team played for in that match?

[solid7]

Well then, exactly what is the detail level of this 10-years-worth of data? A record for each player for each match for each season and the team played for in that match?

Player Bio, Team, position played, Season, 20 different individual statistics. Not broken out by match, just a running total for the season. Do not want to track individual matches. Too much work...

Every year, 3 teams are relegated from the League, and 3 more are promoted from the lower league. Easy enough to add the team names. (not that I don't already have almost all possible teams) The player bio is the hardest work, because it requires research. Player name, Date of Birth, Hometown, Height, Weight.

[June7]

If it's given to you after each match, you are given only match summary, not individual games? I didn't know soccer had matches.

If the player could play different positions throughout the season then should be an attribute in Stats table and records kept at least at match level. If you want to limit each player to a single position forever, then that would be an attribute in the players info table.

Same goes for the team attribute.

You did say who they played for and position are important. Well, that is tied to the match/game entities. Input the match summary data as a record. Then aggregate on players, teams, positions, seasons, whatever.

[solid7]

If it's given to you after each match, you are given only match summary, not individual games? I didn't know soccer had matches.

LOL... Not following you there. Each game played is called a "match". It's played on a "pitch", and the team boss is called the "manager".

If the player could play different positions throughout the season then should be an attribute in Stats table and records kept at least at match level.

The position doesn't change from match to match. From season to season, perhaps But there are some stats that are applicable only to certain positions. Like a Goalkeeper, for example. It would be nice to be able to filter results by position, also. Not sure if this would disrupt that...

If you want to limit each player to a single position forever, then that would be an attribute in the players info table.

I was thinking that it would be a relationship in a more comprehensive table... Mind you, I'm not telling you that I know what I'm talking about - just that it appears to be correct at first thought.

Same goes for the team attribute.

OK, but is this a separate table that is linked to the stats table? I mean, I would actually input the teams into their own table, but use a combo/list box for making the selection, no?

You did say who they played for and position are important. Well, that is tied to the match/game entities. Input the match summary data as a record. Then aggregate on players, teams, positions, seasons, whatever.

So the question again, is that a linked relationship to another table? Sorry to sound like a broken record...

Thanks for your help.

[Dal Jeanis]

Since you are going to account for switching between teams, you could account for switching positions at the exact same place in the architecture.

Player in particular season was Striker for Team A.
Stats for that position were X, Y, Z.

Player in particular season was Goalkeeper for Team B
Stats for that position in that season were X, Y, Z.

Thus, the stats would be linked to a player's position on a team for a season, not just to the player. In the queries, you could aggregate the positions or not, as you chose.

Because you may not have kept all the data separate, you can lump each player's stats for past seasons in the position he generally held, when loading the database. Thus, if someone is occasionally a goalkeeper, he may have earned goalie-specific stats even though they are attached to his striker record.

[June7]

Tennis has games, sets, match. Darts has games and matches. When you said 'match' that's the model I pictured. Never been around soccer much and when my kid played for 2 years, never heard those terms.

The Stats table is the primary data table.

Then there could be 'lookup' tables that serve as sources for data entry to select player and maybe also team if player can play for different team every match/season.

Yes, there would links on the PK/FK fields between Stats and the lookup tables.

All depends on the level of detail you need to document in order to get the desired output. If player position and team can change from match to match or season to season, then need to save data at appropriate level. However, didn't you say the 10-years of data is not at the match detail, only annual? In that case, must be presumed player played same position for the same team for the entire season. Do you want to continue that?

This is a good place to break while you go review that link referenced by Dal in post 19.

[Dal Jeanis]

I'd suggest you start with something like this. You'll have more fields, but start by loading these, and you'll see whether it's workable.

tblPlayers
  PlayerID    Autokey
  PlayerName  Text  

tblTeams
  TeamID      Autokey
  TeamName    Text

tblSeasons
  SeasonID    Autokey
  SeasonYear  Date
  SeasonDesc  Text 

tblPlayerStats
  PK          Autokey
  SeasonID    FK to tblSeasons
  TeamID      FK to tblTeams
  PlayerID    FK to tblPlayers
  PositionID  FK to tblPositions
  StatCode    FK to tblStatType - 
  StatValue   Number - possibly currency if some stats will be decimals.
  StatNotes   Text 

tblPositions
  PK           AutoKey (optional)
  PositionID   Text, Indexed Key (abbreviated name of Position)
  PositionName Text (full name of Position)

tblStatType
  PK          AutoKey (optional)
  StatCode    Text, Indexed Key (abbreviated name of Stat)
  StatName    Text   (name of stat)
  StatDesc    Memo   (full description, including how calculated)