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.
我有一个由PlayerTeam联结表加入的两个sql表Players和Teams。玩家可以在团队中工作多年。处理这种关系的最佳方法是什么?我从一个PlayerTeamYear表开始,但是对PlayerTeam中两列的外键引用似乎都很笨重,特别是因为我想将它转换为Entity Framework。这必须是一种常见的情况。
Thanks!
2 个解决方案
#1
I would add the year to the PlayerTeam table so it has three columns in its primary key.
我将年份添加到PlayerTeam表中,因此它的主键中有三列。
#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.
上面的建议很好。我实际上在PlayerTeam表中添加了两列:一年和一个代理键作为主键。
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.
可以这样想:玩家表描述了所有玩家,团队表描述了所有团队,而PlayerTeam表描述了玩家和团队之间的关系。这种关系还应该包括球员在特定球队时的情况。
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.
您可能还需要考虑其他一些事项。玩家是否有可能离开团队并且没有加入另一个团队?例如,玩家可以退休还是休息一年,你想记录下来吗?如果是这样,您可能需要考虑在PlayerTeam表中添加另一个日期列,以指示玩家何时离开给定团队。
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.
或者那种效果。这可能不是特别有效(您可能希望在select查询上运行“explain”),但处理起来非常简单。
#1
I would add the year to the PlayerTeam table so it has three columns in its primary key.
我将年份添加到PlayerTeam表中,因此它的主键中有三列。
#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.
上面的建议很好。我实际上在PlayerTeam表中添加了两列:一年和一个代理键作为主键。
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.
可以这样想:玩家表描述了所有玩家,团队表描述了所有团队,而PlayerTeam表描述了玩家和团队之间的关系。这种关系还应该包括球员在特定球队时的情况。
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.
您可能还需要考虑其他一些事项。玩家是否有可能离开团队并且没有加入另一个团队?例如,玩家可以退休还是休息一年,你想记录下来吗?如果是这样,您可能需要考虑在PlayerTeam表中添加另一个日期列,以指示玩家何时离开给定团队。
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.
或者那种效果。这可能不是特别有效(您可能希望在select查询上运行“explain”),但处理起来非常简单。