CODEWITHSUNDEEP
mysqldatabase
denski
denski

Reputation: 2040

DB Structure - A user belonging to multiple teams

If I have a users table with email and login details etc, If a user only belongs to one team I can create a new column called team but if that user is on multiple teams how would you structure it?

The best way I can come up with is having a table for that specific user called PlayersTeams but at scale that seem like a large number of tables to be creating. Here is the best way I can come up with, but I thought that the more experienced here might have a better suggestion for me.

Only one team:

PlayerID   email           TeamID
1          [email protected]    123
2          [email protected]   456
3          [email protected]   789

Multiple Teams:

PlayerID    TeamID
1           123
1           456
1           789
2           123
2           789
3           456
3           789

I doubt it, but should I have a specific users database and then once a user is logged in, then switch to the team database?

Upvotes: 1

Views: 1373

Answers (2)

RMathis
RMathis

Reputation: 590

The problem is the relationship between team and player is a many to many relationship (a team is comprised of zero, one or more players & one player can belong to zero, one or more teams).

The only way to resolve a many to many relationship is through an association table (like the PlayerTeam table your instincts are leading you to).

The benefit of the association table is that you can add addition columns such as position, jersey number, etc...

The association table would be comprised of primary keys of both tables with foreign keys back to the original table...

PlayerTeam
----------------------
PlayerID    same datatype and foreign key references to player(player_id)
TeamID      same datatype and foriend key references to team(TeamID)
team_number -- possible additional column for this association
position    -- possible additional column for this association

It would be recommended to create the primary key (PlayerID, TeamID)

Upvotes: 1

lumatijev
lumatijev

Reputation: 96

Many-to-Many relationship is what you are looking for.

Using a third (junction) table would be the correct way to accomplish this scenario. Example table structure:

player: player_id, email
team: team_id, name
player_teams: player_id, team_id

Upvotes: 3

Related Questions