SQLite 3.27

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE user (id INTEGER NOT NULL);  -- other fields
INSERT INTO user VALUES (1),(2),(3),(4),(5),(6),(7),(8);
CREATE TABLE teams (
  id INTEGER SERIAL,
  player1 INTEGER REFERENCES User(id) NOT NULL,
  player2 INTEGER REFERENCES User(id) NOT NULL,
  rating INTEGER NOT NULL,
  PRIMARY KEY (id),
  CHECK (player1 != player2)
);
WITH cte1 AS 
(
  SELECT u1.id AS u1id, u2.id AS u2id FROM user u1, user u2
  WHERE u1.id != u2.id
)
INSERT INTO teams (player1, player2, rating)
SELECT u1id, u2id, 5 FROM cte1;
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
);
INSERT INTO matches (id, team_1, team_2) VALUES (1, 1, 2), (2, 1, 3), (3, 1, 4), (4, 1, 5), (5, 1, 6);
CREATE TABLE match_winner
(
  match_id INTEGER NOT NULL,
  team_id  INTEGER NOT NULL,
  winning_score INTEGER NOT NULL CHECK (winning_score > 0),

  CONSTRAINT 'match_winner_pk' PRIMARY KEY (match_id, team_id),
  CONSTRAINT 'mw_match_fk'     FOREIGN KEY (match_id) REFERENCES matches (match_id),
  CONSTRAINT 'mw_team_fk'      FOREIGN KEY (team_id)  REFERENCES teams   (team_id) 
);
INSERT INTO match_winner VALUES (1, 2, 7), (3, 4, 33), (5, 1, 56);
SELECT t.player1, t.player2, m.id, mw.match_id, mw.team_id, mw.winning_score
FROM teams t
JOIN matches m ON t.player1 = m.team_1 AND t.player2 = m.team_2
JOIN match_winner mw ON m.id = mw.match_id;


player1
player2
id
match_id
team_id
winning_score


1
2
1
1
2
7

1
4
3
3
4
33

1
6
5
5
1
56


CREATE TABLE team (
  -- id INTEGER SERIAL,
  player1 INTEGER REFERENCES User(id) NOT NULL,
  player2 INTEGER REFERENCES User(id) NOT NULL,
  rating INTEGER NOT NULL,
  PRIMARY KEY (player1, player2),
  CHECK (player1 != player2)
);
WITH cte1 AS 
(
  SELECT u1.id AS u1id, u2.id AS u2id FROM user u1, user u2
  WHERE u1.id != u2.id
)
INSERT INTO team (player1, player2, rating)
SELECT u1id, u2id, 5 FROM cte1;
SELECT t.player1, t.player2, m.id, mw.match_id, mw.team_id, mw.winning_score
FROM team t
JOIN matches m ON t.player1 = m.team_1 AND t.player2 = m.team_2
JOIN match_winner mw ON m.id = mw.match_id;


player1
player2
id
match_id
team_id
winning_score


1
2
1
1
2
7

1
4
3
3
4
33

1
6
5
5
1
56