How do I correctly design a VS matching table in PostgreSQL?

CREATE TABLE matches(
 match_id bigserial PRIMARY KEY,
 tournament_id int NOT NULL,
 step int NOT NULL,
 winner match_winner,
 (etc. etc.)
 UNIQUE(match_id, tournament_id, step), - Actual primary key
 FOREIGN KEY (tournament_id) references tournaments(tournament_id)
 ON DELETE RESTRICT
 ON UPDATE CASCADE
);

CREATE TABLE match_players(
 match_id bigint NOT NULL,
 tournament_id int NOT NULL,
 step int NOT NULL,
 player_id int NOT NULL,
 first boolean NOT NULL,
 PRIMARY KEY (match_id, tournament_id, step, player_id),
 UNIQUE (tournament_id, step, player_id),
 foreign key (match_id, tournament_id, step) - keep em together
 references matches(match_id, tournamen t_id, step)
 ON DELETE RESTRICT
 ON UPDATE CASCADE,
 foreign key (player_id) references accounts(player_id)
 ON DELETE RESTRICT
 ON UPDATE CASCADE
);

-- Partial index, ensure no more than one "first" player exists per match
CREATE UNIQUE INDEX idx_match_players_primary
 ON match_players
 USING btree
 (match_id, tournament_id, step)
 WHERE first=true;

-- Also ensure that no more than one "not-first" player exists per match
CREATE UNIQUE INDEX idx_match_players_not_primary
 ON match_players
 USING btree
 (match_id, tournament_id, step)
 WHERE first=false;

I have considered this problem, and I have not come up with something better. Then let me describe my problem, My current solution, and what I want to improve. I also have some concerns, such as whether my design is actually normalized.

I am creating a database and I want to store VS for the game Match information. For simplicity, we pretend that they are chess matches. 1V1. My current design is as follows:

CREATE TABLE matches(
 match_id bigserial PRIMARY KEY,
 tournament_id int NOT NULL,
 step int NOT NULL,
 winner match_winner,
 (etc. etc.)
 UNIQUE(match_id, tournament_id, step) , - Actual primary key
 FOREIGN KEY (tournament_id) references tournaments(tournament_id)
 ON DELETE RESTRICT
 ON UPDATE CASCADE
);

CREATE TABLE match_players(
 match_id bigint NOT NULL,
 tournament_id int NOT NULL,
 step int NOT NULL,
 player_id int NOT NULL,
 first boolean NOT NULL,
 PRIMARY KEY (match_id, tournament_id, step, player_id),
 UNIQUE (tournament_id, step, player_id),
 foreign key (match_id, tournament_id, step) - keep em together
 references matches (match_id, tournament_id, step)
 ON DELETE RESTRICT
 ON UPDATE CASCADE,
 foreign key (player_id) references accounts(player_id)
 ON DELETE RESTRICT
 ON UPDATE CASCADE 
);

-- Partial index, ensure no more than one " first" player exists per match
CREATE UNIQUE INDEX idx_match_players_primary
 ON match_players
 USING btree
 (match_id, tournament_id, step)
 WHERE first=true;
 
-- Also ensure that no more than one "not-first" player exists per match
CREATE UNIQUE INDEX idx_match_players_not_primary
 ON match_players
 USING btree
 (match_id, tournament_id , step)
 WHERE first=false;

To get the actual vs match, I can simply connect match_players to itself (in mp1.match_id = mp2.match_id, mp1.first = true and mp2.first = false, where mp1 and mp2 are two matching instances). Part of the unique index ensures that at most two players can be added.

The database has been normalized in this way, because the players Is unordered. Like, A vs B is the same as B vs A. I have added the “first” boolean to the match so that A vs B can always be displayed. (I think I can simplify it so that mp1.player_id< mp2.player_id ... but the "first" boolean seems to be useful). tournament_id and step are repeated in the second table because they are needed on the Unique index of the table... to ensure that the player has only each step A match. This is my main problem:
>Currently there may be isolated rows in the first table
(matches). There should be two players in a match. In
especially if If there is a match in the matching table, you can
there is no row matching it in the match_players table. Is there a way to
make sure that there are two related rows matching ALWAYS
matches_players? With the “first” method, I am absolutely limited
the number of players per game is less than 2…so figure out
the method of ensuring a minimum of 2 players can solve the problem.

This is me One concern:

>Since the orphans still exist, are there any other data anomalies
what will appear in this design? I am a little uncomfortable with the composite (triple) primary key in
match_players, but I think the
composite foreign_key requirement covers my table.

Thanks to anyone who can help me. This is so far The best thing I can do. I think if I solve the orphaned row problem, then this design will be perfect. I think I can set up a cron job to clear the orphaned row, but I want to know before solving this problem Is there a cleaner design?

I do think that checking subqueries in constraints can solve the problem, but, I don’t think PostgreSQL actually supports this feature.

This is what I call “forward-looking problems”, that is, you may encounter problems with data constraints that rely on rows that have not been inserted. The entire transaction has requirements that each row does not need. Most databases provide There are few tools to solve this problem. Fortunately, PostgreSQL provides you with some options.

Use TOAST’s denormalized “input buffer”

The first The method is to add a column to matches of match_player[] type matches. Then you will store a series of players in the match. This will be implemented to the match_player table using triggers. Although there are severe penalties in terms of development and foreseeing corner cases. I I do think this is a viable option, but it is not an ideal choice. This avoids the forward-looking constraints by flattening the table. But it can only store the 0th step record. Once people are moving… it must be passed only by inserting match_players To complete.

Constrained Trigger

The second method is to create a trigger function that runs once in each statement as the initial delay executed at the end of the transaction Delayable constraint triggers. This will extract system columns from the table to find inserted rows, and then check to make sure that the match occurs in another table. This may be the best general method to solve the problem of forward-looking constraints.