Junction Table with Subtable in SQL

Question

I have two sql tables Players and Teams joined by a PlayerTeam junction table. A player can be on a team for various years. What's the best way to handle this relationship? I started with a PlayerTeamYear table, but the foreign key reference to both columns in PlayerTeam seems unwieldy, especially as I want to convert this to Entity Framework. This has got to be a common scenario.

Thanks!

Answer 1

I would add the year to the PlayerTeam table so it has three columns in its primary key.

Answer 2

The suggestion above is a good one. I'd actually add two columns to the PlayerTeam table: a year and a surrogate key to act as your primary key.

Think of it like this: The player table describes all the players, the team table describes all the teams, and the PlayerTeam table describes the the relationship between players and teams. This relationship should also include when the players were on a given team.

There are a few other things you may want to consider. Is it possible for a player to have left a team and not joined another one? For example, can a player retire or take a year off, and do you want to record this? If so, you may want to consider adding yet another date column to your PlayerTeam table indicating when a player left a given team.

If you're worried about the calls getting complex, you might consider creating your tables such that you can join them using natural joins. Natural joins, if they're available, are joins on two tables in which there are columns with the same name. Generally, the way I like addressing this problem is like this (please pardon the pseudocode):

PlayerTable:
(int) player_id PRIMARY KEY AUTOINCREMENT
...
other data

TeamTable:
(int) team_id PRIMARY KEY AUTOINCREMENT
...
other data

PlayerTeamTable:
(int) player_team_id PRIMARY KEY AUTOINCREMENT
(int) player_id FOREIGN KEY references PlayerTable(player_id)
(int) team_id FOREIGN KEY references TeamTable(team_id)
(datetime) joined_team
(datetime) left_team

This way, you can run a simple query to find everyone who is currently part of a particular team:

select * from PlayerTable natural join TeamTable, PlayerTeamTable where team_name = 'Liverpool' and left_team = NULL;

Or something to that effect. This may not be particularly efficient (you may want to run "explain" on the select query), but it's quite simple to handle.