Many-to-many relationship - how best to implement? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

Many-to-many relationship - how best to implement?

Author

Topic

swordfish
Starting Member

11 Posts

Posted - 2003-05-22 : 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

790 Posts

Posted - 2003-05-22 : 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

479 Posts

Posted - 2003-05-22 : 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 - 2003-05-22 : 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

790 Posts

Posted - 2003-05-22 : 09:50:39

Looks sensible to me.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources