Will this design work

[Andyjones]

League and Results Database



Home Team & Away Team
Home Team Players & Away Team Players
Home Player Score Away Player Score
Fixture
Division

Tables would look something like this.

Teams
Timid
Team Name
Divide


Division ID
Division

PlayersID
Player Name
TeamID

FixureID
Date
Fixture Type
HomeTeamID
Away Team ID
Player ID
Score

How would the relationship be set up and would I need duplicate table ofr fixtures and players and teams so when entering data or results the main form would need to look like this.

Div Date FixtureType

HometeamID VS Away TeamID

Playername Score Playername Score

[Dal Jeanis]

I assume that "fixture" is British terminology for a scheduled game. I don't know what fixture types there might be, but you probably want a table for the types.

Also, since more than one player per team will have a chance to score in just about any type of sport, I'd break up the player scores into a separate table from the fixture table.

There are other possible changes that you might make, depending on how you plan to use the database, but for now I just added some optional notes fields anywhere I think a typical sports statistics enthusiast would want them. As in, everywhere.

tblTeams
  TeamID
  TeamName
  DivID
  Notes 

tblDivisions
  DivID 
  DivName
  Notes

tblPlayers
  PlayerID
  PlayerName
  TeamID
  Notes

tblFixtures
  FixtureID
  FixDate
  FixtureType
  HomeTeamID
  AwayTeamID
  Notes

tblFixTypes
  FixTypeID
  FixTypeName
  Notes

tblFixScores
  FixtureID
  PlayerID
  PlayerScore
  Notes

If players are traded around in your leagues, and if you plan to track more than one season, then you might want to break out the player and team info something like this -

tblPlayers
  PlayerID
  PlayerName
  Notes

tblPlayerTeam
  PlayerID
  TeamID
  PTStartDate
  PTEndDate
  Notes

The same caveat goes if the teams change leagues very often.