I have some tables that benefit from many-to-many tables. For example the team table.
我有一些表可以从多对多表中受益。例如团队表。
Team member can hold more than one 'position' in the team, all the positions are listed in the position db table. The previous positions held are also stored for this I have a separate table, so I have
团队成员可以在团队中持有多个“职位”,所有职位都列在职位数据库表中。以前持有的头寸也存放了这个我有一个单独的表,所以我有
- member table (containing team details)
- positions table (containing positions)
- member_to_positions table (id of member and id of position)
- member_to_previous_positions (id of member and id of position)
成员表(包含团队详细信息)
职位表(包含职位)
member_to_positions表(成员的id和位置的id)
member_to_previous_positions(成员的id和位置的id)
Simple, however the crux comes now that a team member can belong to many teams aghhh. I already have a team_to_member look-up table. Now the problem comes how do I tie a position to a team? A member may have been team leader on one team, and is currently team radio man and press officer on a different team. How do I just pull the info per member to show his current position, but also his past history including past teams. Do I need to add a position_to team table and somehow cross reference that, or can I add the team to the member to positions table?
很简单,但现在的关键是团队成员可以属于许多团队。我已经有一个team_to_member查找表。现在问题是如何将一个职位与团队联系起来?一名成员可能是一个团队的团队领导,目前是另一个团队的团队电台人员和新闻官。我如何只是提取每个成员的信息以显示他当前的位置,以及他过去的历史,包括过去的球队。我是否需要添加一个position_to团队表并以某种方式交叉引用,或者我可以将团队添加到成员到职位表?
It's all very confusing, this normalization.
一切都非常令人困惑,这种正常化。
6 个解决方案
#1
It's perfectly legitimate to have a TeamPositionMember table, with the columns
拥有一个包含列的TeamPositionMember表是完全合法的
Team_Id
Position_Code
Member_Id
Start_Date
End_Date NULLABLE
And and a surrogate ID column for Primary Key if you want; otherwise it's a 3-field composite Primary Key. (You'll want a uniqueness constraint on this anyway.)
如果您愿意,还可以使用主键的代理ID列;否则它是一个3场复合主键。 (无论如何,你会想要一个唯一性约束。)
With this arrangement, you can have a team with any set of positions. A team can have zero or more persons per position. A person can fill zero or more positions for zero or more teams.
通过这种安排,您可以拥有一组任何职位。团队每个职位可以有零个或多个人。一个人可以为零个或多个团队填补零个或多个职位。
EDIT:
If you want dates, just revise as shown above, and add Start_Date to the PK to allow the same person to hold the same position at different times.
如果您想要日期,只需按上图所示进行修改,并将Start_Date添加到PK,以允许同一个人在不同时间保持相同的位置。
#2
Yes, a many-to-many junction table can have additional attributes (columns).
是的,多对多联结表可以具有其他属性(列)。
For example, if there's a table called PassengerFlight table that's keyed by PassengerID and FlightID, there could be a third column showing the status of the given passenger on the given flight. Two different statuses might be "confirmed" and "wait listed", each of them coded somehow.
例如,如果有一个名为PassengerFlight表的表由PassengerID和FlightID键控,则可能会有第三列显示给定航班上给定乘客的状态。两种不同的状态可能被“确认”和“等待列出”,每种状态都以某种方式编码。
In addition, there can be ternary relationships, relationships that involve three entities and not just two. These tables are going to have three foreign keys that taken together are the primary key for the relationship table.
此外,可能存在三元关系,涉及三个实体的关系,而不仅仅是两个。这些表将有三个外键,它们是关系表的主键。
#3
My first thought:
我的第一个想法:
Give your many-to-many teams/members table an ID column. Every team-to-member relationship now has an ID.
为您的多对多团队/成员表提供ID列。每个团队到成员的关系现在都有一个ID。
Then create a many-to-many linking positions to team-member relationships.
然后为团队成员关系创建多对多链接位置。
This way, teams can have multiple members, members can have multiple teams, and members can have multiple positions on a per-team basis.
这样,团队可以拥有多个成员,成员可以拥有多个团队,成员可以在每个团队中拥有多个职位。
Now everything is nice and DRY, and all the linking up seems to work. Does that sound right to anyone else?
现在一切都很好干,所有连接似乎都有效。这对其他人来说听起来不错吗?
#4
Sounds like you need a many-to-many positions to teams table now.
听起来你现在需要一个多对多的职位来参加球队的比赛。
#5
Your team_to_member table can indeed have an extra column position_id to describe (or in this case point to) the position the member has within that team.
您的team_to_member表确实可以有一个额外的列position_id来描述(或者在这种情况下指向)该成员在该团队中的位置。
#6
Get rid of member_to_previous_position table. Just use member_to_positions and have these columns:
摆脱member_to_previous_position表。只需使用member_to_positions并拥有以下列:
MemberToPositionID (autoincrement OK only)
MemberID
PositionID
StartDate
EndDate
Then to find current positions, you do:
然后找到当前位置,你做:
select *
from member_to_positions
where EndDate is null
#1
It's perfectly legitimate to have a TeamPositionMember table, with the columns
拥有一个包含列的TeamPositionMember表是完全合法的
Team_Id
Position_Code
Member_Id
Start_Date
End_Date NULLABLE
And and a surrogate ID column for Primary Key if you want; otherwise it's a 3-field composite Primary Key. (You'll want a uniqueness constraint on this anyway.)
如果您愿意,还可以使用主键的代理ID列;否则它是一个3场复合主键。 (无论如何,你会想要一个唯一性约束。)
With this arrangement, you can have a team with any set of positions. A team can have zero or more persons per position. A person can fill zero or more positions for zero or more teams.
通过这种安排,您可以拥有一组任何职位。团队每个职位可以有零个或多个人。一个人可以为零个或多个团队填补零个或多个职位。
EDIT:
If you want dates, just revise as shown above, and add Start_Date to the PK to allow the same person to hold the same position at different times.
如果您想要日期,只需按上图所示进行修改,并将Start_Date添加到PK,以允许同一个人在不同时间保持相同的位置。
#2
Yes, a many-to-many junction table can have additional attributes (columns).
是的,多对多联结表可以具有其他属性(列)。
For example, if there's a table called PassengerFlight table that's keyed by PassengerID and FlightID, there could be a third column showing the status of the given passenger on the given flight. Two different statuses might be "confirmed" and "wait listed", each of them coded somehow.
例如,如果有一个名为PassengerFlight表的表由PassengerID和FlightID键控,则可能会有第三列显示给定航班上给定乘客的状态。两种不同的状态可能被“确认”和“等待列出”,每种状态都以某种方式编码。
In addition, there can be ternary relationships, relationships that involve three entities and not just two. These tables are going to have three foreign keys that taken together are the primary key for the relationship table.
此外,可能存在三元关系,涉及三个实体的关系,而不仅仅是两个。这些表将有三个外键,它们是关系表的主键。
#3
My first thought:
我的第一个想法:
Give your many-to-many teams/members table an ID column. Every team-to-member relationship now has an ID.
为您的多对多团队/成员表提供ID列。每个团队到成员的关系现在都有一个ID。
Then create a many-to-many linking positions to team-member relationships.
然后为团队成员关系创建多对多链接位置。
This way, teams can have multiple members, members can have multiple teams, and members can have multiple positions on a per-team basis.
这样,团队可以拥有多个成员,成员可以拥有多个团队,成员可以在每个团队中拥有多个职位。
Now everything is nice and DRY, and all the linking up seems to work. Does that sound right to anyone else?
现在一切都很好干,所有连接似乎都有效。这对其他人来说听起来不错吗?
#4
Sounds like you need a many-to-many positions to teams table now.
听起来你现在需要一个多对多的职位来参加球队的比赛。
#5
Your team_to_member table can indeed have an extra column position_id to describe (or in this case point to) the position the member has within that team.
您的team_to_member表确实可以有一个额外的列position_id来描述(或者在这种情况下指向)该成员在该团队中的位置。
#6
Get rid of member_to_previous_position table. Just use member_to_positions and have these columns:
摆脱member_to_previous_position表。只需使用member_to_positions并拥有以下列:
MemberToPositionID (autoincrement OK only)
MemberID
PositionID
StartDate
EndDate
Then to find current positions, you do:
然后找到当前位置,你做:
select *
from member_to_positions
where EndDate is null