Table Relationship Question

  • Thread starter Thread starter dvlander
  • Start date Start date
D

dvlander

I am in the preliminary stages of building a database to analyze the
performance of basketball teams in a league.

I have a table called "Teams". I am anticipating another table called
"ScheduledGames". My quandary is as follows: The "ScheduledGames" table
will have fields for "Visiting Team" and "Home Team" and both will be
populated from data in the "Teams" table. However, I cannot link both of
these fields to the TeamID field in "Teams", can I? Is there another simple
solution? The design is so important, I'd really like to get this right.

I really appreciate the advice.

Dale

Thanks,

Dale
 
You can join two (or more) fields from the same table to copies of another
table. You could also remove the teams from the Game table and just store the
date, time, location,... and then create a Game/Team table with fields like:

tblGamesTeams
===============
GameTeamID autonumber primary key
GameID links to tblGames.GameID
TeamID links to tblTeams.TeamID
HomeAway stores H or A
Score
 
Duane:

Really appreciate your advice. In your experience, which of the two
scenarios would you say is the most efficient in the long run?

Sincerely,

Dale
 
The tblGamesTeams approach creates greater flexibility. However, for
basketball, you can generally assume there will be only 2 teams unlike a
track or swim meet which could be 2, 3, ...or many more.

If you want to store additional Game/Team attributes such as lockerroom,
attendant,... then I would definitely go with the more normalized structure.

I think I would go with tblGamesTeams.
 
Duane:

Thanks - the tblGamesTeams structure that you have below does not show a
field for the opponent. The reason that is important is because an
evaluation of a team's performance would certainly depend on the opponent. A
10-point win over UCLA is drastically different than a 10-point win over
Podunk State.

Does this additional information change your recommendation at all?

Sincerely,

Dale
 
Back
Top