Table Relationship Question

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 Hookom
Microsoft Access MVP


"dvlander" wrote:

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:

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" wrote:

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 Hookom
Microsoft Access MVP


"dvlander" wrote:

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

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 Hookom
Microsoft Access MVP


"dvlander" wrote:

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" wrote:

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 Hookom
Microsoft Access MVP


"dvlander" wrote:

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:

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" wrote:

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 Hookom
Microsoft Access MVP


"dvlander" wrote:

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" wrote:

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 Hookom
Microsoft Access MVP


"dvlander" wrote:

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

The opponents will have the same GameID values.

--
Duane Hookom
Microsoft Access MVP


"dvlander" wrote:

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" wrote:

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 Hookom
Microsoft Access MVP


"dvlander" wrote:

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" wrote:

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 Hookom
Microsoft Access MVP


"dvlander" wrote:

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