SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Many-to-many relationship - how best to implement?
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

swordfish
Starting Member

11 Posts

Posted - 05/22/2003 :  05:19:54  Show Profile
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  Show Profile
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.

Go to Top of Page

dsdeming
Constraint Violating Yak Guru

USA
479 Posts

Posted - 05/22/2003 :  08:02:12  Show Profile
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

Go to Top of Page

swordfish
Starting Member

11 Posts

Posted - 05/22/2003 :  09:48:14  Show Profile
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

Go to Top of Page

Andraax
Aged Yak Warrior

Sweden
790 Posts

Posted - 05/22/2003 :  09:50:39  Show Profile
Looks sensible to me.

Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.38 seconds. Powered By: Snitz Forums 2000