Avoiding multiple Relationships

Marden Lee · February 12th, 2005, 01:25 PM

Hello everyone,

I'm somewhat new to SQL and i'm facing a problem that I'm sure could be of an easier solution.

Right now I have a table with players names and stats for each player. I have a second table called 'Teams' that will have, among other things, 22 fields, each one with a player code referred by the 'Players' table.

How do I establish the relationship? It's a 1 to many, mind you. It will actually be possible for a player to be in more than one team. Each team is a person owned AFL (Australian Football League) "dream team". But do I really have to establish 22 relationships between the players table and the team table? Or the problem is in the players team and I shouldn't be having 22 fields, one for each player? In that case how do I go about changing it?

Thank you,
Marden Lee

Justin Cave · February 12th, 2005, 06:48 PM

Normally, you would would not create the TEAMS table with 22 player columns. You would create a PLAYER table with player information, a TEAM table with team information, and a TEAM_PLAYER table that maps players to teams. Something like

Code:

CREATE TABLE team (
  team_id   NUMBER PRIMARY KEY,
  name      VARCHAR2(100),
  owner     VARCHAR2(100)
);

CREATE TABLE player (
  player_id  NUMBER PRIMARY KEY,
  name       VARCHAR2(100)
);

CREATE TABLE team_player (
  team_id    NUMBER REFERENCES team( team_id ),
  player_id  NUMBER REFERENCES player( player_id )
);

Justin
Distributed Database Consulting, Inc.
http://www.ddbcinc.com/askDDBC

deniscuba · February 15th, 2005, 09:15 AM

In this case, if each player belong to team player,then the perfect diagram will be :

team (
       team_id <sqlType> PRIMARY KEY,
       name <sqlType>,
       owner <sqlType>
      )

player (
         player_id <sqlType> PRIMARY KEY,
         name <sqlType>,
         team_id <sqlType>
)
where team_id is foreing key that references to team table

Justin Cave · February 15th, 2005, 12:00 PM

Except that the original requirement envisions one player on an arbitrarily large number of teams. That's the reason that you need the mapping table TEAM_PLAYER (which would probably be better named ROSTER).

Justin
Distributed Database Consulting, Inc.
http://www.ddbcinc.com/askDDBC

Marden Lee · February 16th, 2005, 08:30 AM

Yes that is indeed the case.
Thank you Justin. That was exactly what I was missing; a 3rd table to establish the relationships.