CODEWITHSUNDEEP
sqlpowerdesigner
user1255410
user1255410

Reputation: 886

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.

Upvotes: 1

Views: 65

Answers (2)

Marcelo Cantos
Marcelo Cantos

Reputation: 185988

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.

Upvotes: 1

zedfoxus
zedfoxus

Reputation: 37109

I'd recommend you create three tables: teams, players, team_players.

Teams

create table teams (
    id int not null auto_increment,
    teamname varchar(100) not null,
    country varchar(100) not null,
    primary key (id)
);

Players

create table players (
    id int not null auto_increment,
    firstname varchar(100) not null,
    lastname varchar(100) not null,
    nickname varchar(100),
    country varchar(100) not null,
    fieldposition varchar(100), -- can be an int FK to a fieldpositions table
    debutdate date,
    primary key (id)
);

Team_players

create table team_players (
    id int not null auto_increment,
    teamid int not null,
    playerid int not null,
    primary key (id),
    constraint uk_team_players_rel unique (teamid, playerid),
    constraint fk_team_players_teamid foreign key (teamid) references teams (id),
    constraint fk_team_players_playerid foreign key (playerid) references players (id)
);

Example (MySQL): http://sqlfiddle.com/#!9/7c4ff8

Upvotes: 0

Related Questions