I am currently trying to setup a database managing players and teams for a competition. Players are competing in teams of two but may be shuffled.
As every team combination can only exist once, I decided that the primary key of each team is composed of the two ids of the players in it:
CREATE TABLE teams ( player1 INTEGER REFERENCES User(id) NOT NULL, player2 INTEGER REFERENCES User(id) NOT NULL, rating INTEGER NOT NULL, PRIMARY KEY (player1, player2) );
The competition consists of matches, each match being three games.
I want to reference two teams in a match:
CREATE TABLE matches ( id INTEGER PRIMARY KEY NOT NULL, team_1 INTEGER REFERENCES Team(id) NOT NULL, team_2 INTEGER REFERENCES Team(id) NOT NULL, timestamp DATETIME DEFAULT CURRENT_TIMESTAMP )
Each game contains the score of the winning and the losing team:
CREATE TABLE games( id INTEGER PRIMARY KEY NOT NULL, match_id: INTEGER REFERENCES Match(id) NOT NULL, score_winner : INTEGER NOT NULL, score_loser : INTEGER NOT NULL )
As I do not know the winner of a game like this, I would like to add the primary key of the team that won the game to be able to to figure out the winner of a match (whoever won the most matches)/
How do I reference the winner of a game in the given table?