Skip to content
Advertisement

SQL one-to-many relationship: Should I see a “many” field in the table?

This is something basic which drives me crazy:

If for example I have a table “team” and a table “player”.

And the team can have multiple players in a one-to-many relation.

Should this be expressed in a dedicated field in the team table? I mean, should there be a “players” column in the team table?

Or is the definition of the foreign key that links the two tables sufficient?

CREATE TABLE team (
  id serial,
  name VARCHAR(100) NOT NULL,
  PRIMARY KEY (id)
);

CREATE TABLE player (
  id serial,
  team_id int NOT NULL,
  name VARCHAR(100) NOT NULL,
 
  PRIMARY KEY (id),

  FOREIGN KEY (team_id) REFERENCES team(id) 
);

I’ll be most thankful for a short explanation. (I work with PostgreSQL if that matters.)

Thanks!

Advertisement

Answer

“Should there be a “players” column in the team table?” No! You want the team column in the players table that you already have. That basically says “a team can have many players but a player can only be on 1 team”. If you then put a players column in team then you have created a 1:1. That says “a player can only be on 1 team, and a team can only have 1 player”.
The above would be the to define current situation. But can a player be on multiple teams – especially over time – and you want to keep that history: player A is now on team C, but last season was on team B. Then you have a M:M and need a 3rd table (player_team) with a column for player and a column for team along with some indicator defining when it is/was effective – like start and end dates.

User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement