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

Table: Team 
t_id       PK

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.

  • You'll need a unique index on (t_id, p_id). Or drop the r_id and use it as PK. (I assume that a player can belong to a team only once). Other than that, yes, it's normal for a many-to-many relationship, to have such a table. – ypercubeᵀᴹ Mar 3 '15 at 20:22
  • @ypercube see my edit above - I think it confirms what you commented:) - Are you suggesting using a composite key of p_id and t_id in a separate table? – Alex Mar 3 '15 at 20:22
  • "multiple teams can have one roster" This cannot happen, as r_id is the PK. – ypercubeᵀᴹ Mar 3 '15 at 20:24
  • So I should drop r_id and compose the roster table using a composite of p_id and t_id? - Sorry, new concept to me :) – Alex Mar 3 '15 at 20:25
  • 1
    I'm suggesting using a composite key (constraint really which is enforced by an index) on table Roster. Yes, you can drop the r_id altogether - or keep it. The choice is yours. – ypercubeᵀᴹ Mar 3 '15 at 20:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.