| Author |
 Topic  |
|
|
swordfish
Starting Member
11 Posts |
 Posted - 05/22/2003 : 05:19:54
|
I am creating a database with game schedules for teams. I have the following Teams table
Teams
-----
TeamID int pk
TeamName varchar(200)
I am unsure how to implement the Games table. A team can have many games and a game can have 2 teams. Am I best to create a Games table and a TeamsGames junction table, like this -
Games
-----
GameID int pk
GameDate date
GameTime varchar(5)
TeamID int
TeamsGames
----------
GameID int
TeamID int
or, have a games table showing the home team and away team, since there can be only 2 teams in a game -
Games
-----
GameID int pk
GameDate date
GameTime varchar(5)
HomeTeam int
AwayTeam int
I think I can use both methods, but I'm unsure what one will be the most scalable. Any help will be much appreciated.
Thanks a lot,
Al
|
|
|
Andraax
Aged Yak Warrior
Sweden
790 Posts |
Posted - 05/22/2003 : 07:55:50
|
I think either will serve you nicely. However, if you use the TeamsGames approach, you don't need the TeamID in the Games table. You should also add a column to the TeamsGames table specifying if the team was home or away.
That said, I think I would go with the other approach. It makes more sense.
|
 |
|
|
dsdeming
Constraint Violating Yak Guru
USA
479 Posts |
Posted - 05/22/2003 : 08:02:12
|
If you use a junction table, it needs a column for indicating home vs visitor. However, since what you have is a many-to-two relationship, you'd also need some mechanism for limiting the number of teams in a game to two.
Having the Games table show both teams makes more sense to me.
HTH
|
|
|
|
swordfish
Starting Member
11 Posts |
Posted - 05/22/2003 : 09:48:14
|
Hi guys,
Thanks a lot for that, thats great help.
Another quick question about the best way to query the Game table. Is the following the most sensible way of doing? Joining the Game table to the Team table twice, once for each foreign key field (hometeam and awayteam).
select GameDate, GameTime, t.TeamName as Home, tt.TeamName as Away
from teams t inner join games g
on g.hometeam = t.teamid
inner join teams tt
on g.awayteam = tt.teamid
where gameid = 1
Ta,
Al
|
|
|
|
Andraax
Aged Yak Warrior
Sweden
790 Posts |
Posted - 05/22/2003 : 09:50:39
|
Looks sensible to me.
|
|
|
| |
Topic |
|
Many-to-many relationship - how best to implement?
Forum Locked
