I'm trying to design a DB and one of my tables is something like this:
ID_tourn | ID_player | and other attributes...
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
(ID_tourn, ID_player2)for the second
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) );