p0kero p0kero - 1 month ago 11
SQL Question

Can I avoid "redundancy" in this Foreign Key?

I'm trying to design a DB and one of my tables is something like this:

Table player_in_tournament:

ID_tourn | ID_player | and other attributes...


and the PK is the two first columns.

In table tournament_game (which represents a game/match of the tournament) I want to reference 2 "objects" from the player_in_tournament, so I should have two foreign keys, and each of the are composed by a ID_player and a ID_tour. But the ID_tour is the same in both "objects".

The question is if it's a good idea to change the PK of the first table and add a ID_player_in_tournament, and use it like foreign key in the tournament_game table.

Answer

Adding a surrogate key to player_in_tournament table makes it possible to construct a tournament_game that represents players with two different ID_tourn. This avoids redundancy at the expense of allowing inconsistency.

You should have three fields participating in two foreign keys -

  • (ID_tourn, ID_player1) for the first player_in_tournament row
  • (ID_tourn, ID_player2) for the second player_in_tournament row

Note that the same column ID_tourn participates in two foreign key relationships:

create table player_in_tournament(
    ID_tourn int
,   ID_player int
,   ...
,   primary key(ID_tourn, ID_player)
);
create table tournament_game(
    ID_tourn int
,   ID_player1 int
,   ID_player2 int
,   ...
,   foreign key (ID_tourn, ID_player1)
        references player_in_tournament(ID_tourn, ID_player)
,   foreign key (ID_tourn, ID_player2)
        references player_in_tournament(ID_tourn, ID_player)
);
Comments