sql - 如何在 PostgreSQL 中正确设计 VS 匹配表？

Question

我已经对此进行了一些思考，但我还没有想出更好的办法。那么让我描述一下我的问题、我目前的解决方案以及我想改进的地方。我也有一些顾虑，比如我的设计是否真的规范化了。

我正在制作一个数据库，我想在其中存储锦标赛的 VS 匹配信息。为简单起见，我们假设它们是国际象棋比赛。 1v1。我目前的设计如下:

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;

为了获得实际的 vs 匹配，我可以简单地将 match_players 连接到自身(在 mp1.match_id = mp2.match_id 和 mp1.first = true 和 mp2.first = false 上，其中 mp1 和 mp2 是匹配的两个实例).部分唯一索引确保最多可以添加两个玩家。

数据库已经以这种方式标准化，因为玩家是无序的。 A vs B 与 B vs A 相同。我在匹配项中添加了“第一个” bool 值，以便始终显示 A vs B。 (我想我可以简化它，使 mp1.player_id < mp2.player_id... 但“第一个” bool 值似乎有效)。

tournament_id 和 step 在第二张表中重复，因为该表的唯一索引需要它们...以确保玩家在锦标赛的每个步骤中只有一场比赛。

这是我的主要问题:

1. 目前可能在第一个表中有孤立行(火柴)。一场比赛应该只有两名球员参加。在特别是，如果匹配表中存在匹配项，则有可能在 match_players 表中没有匹配它的行。有没有确保匹配总是有两个关联行的方法matches_players ？使用“第一种”方法，我绝对限制了每场比赛的球员人数少于 2 人......所以弄清楚一种确保最少 2 名玩家就能解决问题的方法。

这是我的担忧之一:

• 由于孤立行仍然存在，是否有任何其他数据异常在这个设计中会出现什么？我有点不舒服match_players 中的复合(三重)主键，但我认为复合外键要求满足了我对该表的要求。

---

感谢任何能帮助我的人。这是迄今为止我能做的最好的。我想如果我解决了孤立行的问题，那么这个设计就完美了。我想我可以设置一个 cron 作业来清除孤立的行，但我想知道在确定这个之前是否存在更简洁的设计。

我确实认为检查约束中的子查询可以解决问题，但遗憾的是，我不认为 PostgreSQL 实际上还支持该功能。

Answer 1

这就是我所说的“前瞻性问题”，即您可能会遇到有关依赖于尚未插入的行的数据约束的问题。整个事务具有个别行没有的要求。大多数数据库几乎没有提供解决此问题的工具。幸运的是，PostgreSQL 为您提供了一些选择。

使用 TOAST 的非规范化“输入缓冲区”

第一种方法是在匹配项中添加一列，称为 match_player[] 类型的匹配项。然后，您将在此处存储比赛中的一系列球员。这将使用触发器具体化到 match_player 表。尽管在开发和预见极端情况方面，这会产生重大影响。我确实认为它是一个可行的选择，但它不是一个理想的选择。这通过展平表格避免了前瞻性约束。但是它只能存储步骤 0 的记录。一旦人们开始行动......那必须通过仅插入 match_players 来完成。

约束触发器

第二种方法是创建一个触发器函数，它作为 INITIALLY DEFERRED DEFERRABLE CONSTRAINT TRIGGER 在最后执行的交易。这将从表中提取系统列以查找插入的行，然后检查以确保匹配出现在另一个表中。这可能是解决前瞻性约束问题的最佳通用方法。