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.
Aucun commentaire:
Enregistrer un commentaire