Table Relationship Question

[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

 

[Duane Hookom]

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

 

[dvlander]

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

 

[Duane Hookom]

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.

 

[dvlander]

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

 

[Duane Hookom]

The opponents will have the same GameID values.