How to create VS correspondence table in PostgreSQL?

Question

I thought about it, and I could not come up with anything better. So let me describe my problem, my current solution, and what I would like to improve. I also have a few problems, for example, is my project really normalized or not.

I am making a database where I would like to store information about VS Match for tournaments. For simplicity, let's just pretend they are chess matches. 1x1. 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 actual vs matches, I can just attach match_players to myself (on mp1.match_id = mp2.match_id and mp1.first = true and mp2.first = false, where mp1 and mp2 are two instances of matches). Partial unique indexes provide a maximum of two players.

The database has been normalized this way because the players are disordered. Like in, A vs B is the same as B vs A. I added the “first” logic code to match so that A vs B could be displayed sequentially (I think I could simplify it to mp1. player_id <mp2.player_id ... but the "first" logical process seems to work).

The tournament_id and step are repeated in the 2nd table, because they are necessary for the unique index of this table ... so that players have only one match for each step of the tournament.

Here is my main question:

• It is currently possible to have the axis of the orphans in the first table (Matches). There must be exactly two players in the match. In particular, if a match exists in the match table, it is possible that the rows do not match it in the match_players table. Is there any way to make sure ALLWAYS matches have two related lines in match_players? With the “first” method, I definitely limited the number of players per match to less than 2 ... so figuring out a way to provide at least 2 players will solve the problem.

Here is one of my problems:

• Since orphaned rows may still exist, are there any other data anomalies that might arise in this design? I am a bit uncomfortable with the (triple) primary key in match_players, but I think the Compound foreign_key requirement covers me for this table.

---

Thanks to everyone who can help me. This is the best I could do so far. I think that if I solve the problem with orphaned ranks, then this design will be perfect. I suppose I can tweak the cron job to clear the axes of the orphans, but I would like to know if there is a cleaner design before relying on this.

I really think that the subquery in the control constraint will solve the problem, but, alas, I don’t think PostgreSQL actually supports this function.

Answer 1

This is what I call the “prospective problem”, namely that you may have problems with data restrictions that rely on rows that are not yet inserted. The transaction as a whole has requirements that are not on separate lines. Most databases offer several tools to solve this problem. Fortunately, PostgreSQL offers you several options.

Denormalized "input buffer" using TOAST

The first approach was to add a column to the match called match of type match_player[] . Then you can save the array of players in the match. This will be implemented in the match_player table using a trigger. It has significant fines, albeit in terms of the development and foresight of corner affairs. I consider it a viable option, but it is not ideal. This avoids promising restrictions by smoothing the table. However, it can only store entries in stage 0. When people make moves ... this should be done by inserting only into match_players.

Restriction triggers

A second approach would be to create a trigger function that runs once for each statement in the form of an INITIALLY DEFERRED DEFERRABLE CONSTRAINT TRIGGER that executes at the end of a transaction. This will pull the system columns out of the table to find the inserted rows, and then check to make sure that matches occur in another table. This is perhaps the best, general approach to solving the problem of long-term limitations.