How do I go about this relationship?

Question

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.

If you want to guarantee referential integrity then you should have a FK relationship between each of the teamPlayer columns and the playerId in the Players table. — DeanOC, Dec 20 '15 at 01:12
Not sure how to go about this in my modelling software. All I can do is have 1-1 1-many many-1 many-many relationships. How to achieve this? — user1255410, Dec 20 '15 at 01:14
You need to add more context (or tags). What is your modelling software? what is your DB? — DeanOC, Dec 20 '15 at 01:16
I'm working with PowerDesigner first with a conceptual model and then just auto export it to physical. — user1255410, Dec 20 '15 at 01:16

Marcelo Cantos · Accepted Answer · 2015-12-20T08:02:02.700

1

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.

edited Dec 20 '15 at 08:02

answered Dec 20 '15 at 01:53

Marcelo Cantos

181,030
38
327
365

score 0 · Answer 2 · answered Dec 20 '15 at 01:53

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

How do I go about this relationship?

2 Answers2