Can a many-to-many join table contain more than two columns?

Question

I have several tables that benefit from many-to-many tables. For example, a table of commands.

A team member may contain more than one position in the team, all positions are indicated in the db position table. Previous positions are also stored for this. I have a separate table, so I have

• participants table (containing information about the team)
• position table (containing positions)
• member_to_positions table (element identifier and position identifier)
• member_to_previous_positions (element id and position id)

Simple, but now the question arises that a team member can belong to many aghhh teams. I already have a team_to_member lookup table. Now the problem is, how do I attach to a team? The member may have been a team leader on one team, and is currently a radio amateur commander and press officer on another team. How to simply pull out information for each participant to show his current position, as well as his past history, including past teams. Do I need to add the position_to table to the team and somehow cross-reference it, or can I add the command to the member in the position table?

All this is very confusing, this normalization.

Answer 1

It is completely legal to have a TeamPositionMember table with columns

Team_Id Position_Code Member_Id Start_Date End_Date NULLABLE 

And and a column with a surrogate identifier for the Primary key, if you want; otherwise, it is a 3-field composite primary key. (In any case, you will need a unique constraint.)

With this arrangement, you can have a team with any set of positions. A team may have zero or more persons per position. A person may fill in zero or more positions for zero or more teams.

EDIT:

If you need dates, just go as shown above and add Start_Date to PK so that the same person can hold the same position at different times.

Answer 2

Yes, a many-to-many join table can have additional attributes (columns).

For example, if there is a table called the PassengerFlight table with the password PassengerID and FlightID, there may be a third column showing the status of this passenger in this flight. Two different states can be “confirmed” and “wait in line”, each of which is somehow encoded.

In addition, there may be triple relationships, relationships that are associated with three entities, and not just two. These tables will have three foreign keys, taken together, are the primary key for the relationship table.

Answer 3

My first thought:

Give the many-to-many / member identifier column. Each relationship between members now has an identifier.

Then create many-to-many links for member relationships.

Thus, teams can have multiple members, members can have multiple teams, and members can have multiple positions in each team.

Now everything is beautiful and dry, and the whole connection seems to work. Does this sound right to anyone else?

Answer 4

It seems that you need to have many, many positions in the command table now.

Answer 5

In your team_to_member table, there really can be an additional column position_id to describe (or in this case the point) the position that the member has in this team.

Answer 6

Get rid of the member_to_previous_position table. Just use member_to_positions and enter the following columns:

 MemberToPositionID (autoincrement OK only) MemberID PositionID StartDate EndDate 

Then, to find current positions, follow these steps:

 select * from member_to_positions where EndDate is null