Search code examples
sqlpowerdesigner

How do I go about this relationship?

novice in SQL here.

I've got this relationship:

relationship

Teams have references to all 5 members and each player has reference to his team. Now, should I also make teamPlayer1,2,... a FK of PLAYERS entity? Cause so far I've only worked with relationships where simply one attribute corresponded to another, not five to one. Not sure how to go about this.

Solution

  • teamPlayer1–5 are redundant and should be removed. You can reconstruct the list of players by means of a join. If you want to allow only five players per team, augment PLAYERS with teamMember int, UNIQUE(teamId, teamMember), CHECK(teamMember between 0 and 4).

    Correction: You can reconstruct players per team without a join, since the required information is all in the PLAYERS table.