I'm trying to find the best way to architect the relationship in the scenario that:
A team can have multiple players, a player may also have multiple teams
My first thought was to use a lookup table to set the players whom belong to that team, but is there a more efficient way I could define this relationship?
I would normally provide a foreign key from the
Player table to the
Team table, but given a single player can be in multiple teams, I think it makes sense to create a lookup table between the two; below is a simplified skeleton of the schema I am planning:
Table: Player ------------- p_id PK p_name Table: Team ------------- t_id PK t_name Table: Roster ------------- r_id PK t_id FK p_id FK
I have normally been able to model my databases without the need of a look-up table, but in this use case I have a gut feeling that it makes sense to implement one. Any advice would be appreciated.
EDIT My only concern with this implementation, is that when drawn on paper the cardinalities between Roster and Team don't make sense:
multiple teams can have one roster = no, nobody shares a roster...
multiple rosters can belong to one team = no, the team is set, players aren't substituted.