I have a match entity that represent a tennis match, said match can be between two players or two teams. You cant have player vs team, and a player can be in many teams. I've decided to implement this as follows:
CREATE TABLE Player (
player_id INT PRIMARY KEY
);
CREATE TABLE Team (
team_id INT PRIMARY KEY
);
CREATE TABLE player_team (
fk_team_id INT NOT NULL,
fk_player_id INT NOT NULL,
PRIMARY KEY (fk_team_id, fk_player_id),
FOREIGN KEY (fk_team_id) REFERENCES Team(team_id),
FOREIGN KEY (fk_player_id) REFERENCES Player(player_id)
);
CREATE TABLE Match (
match_id INT PRIMARY KEY
);
CREATE TABLE Participant (
participant_id INT PRIMARY KEY,
fk_team INT NULL,
fk_player INT NULL,
fk_match INT NOT NULL,
FOREIGN KEY (fk_match) REFERENCES Match(match_id),
FOREIGN KEY (fk_team) REFERENCES Team(team_id),
FOREIGN KEY (fk_player) REFERENCES Player(player_id),
CHECK (
(fk_team IS NOT NULL AND fk_player IS NULL) OR
(fk_team IS NULL AND fk_player IS NOT NULL)
),
UNIQUE (fk_match, fk_player),
UNIQUE (fk_match, fk_team)
);
How is this pattern called? Per my google research it seems like a weird version of polymorphic association, and the Participant table is acting as a kind of junction table of the 3 entities. I'm also wondering how would I represent this on an ER diagram using Chen's notation.
Asked by Fullaccess
(1 rep)
Sep 7, 2024, 12:26 PM