mardi 3 mars 2015

Is the use of my lookup table efficient?


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