如何在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;

要获取实际的比赛匹配，我可以简单地将match_players自身连接（在mp1.match_id = mp2.match_id且mp1.first = true和mp2.first = false的情况下，其中mp1和mp2是比赛的两个实例）。部分唯一索引确保最多只能添加两名玩家。

数据库以此方式规范化，因为玩家是无序的。例如，A vs B与B vs A相同。我已经在比赛中添加了“first”布尔值，以便可以一致地显示A vs B。（我猜我可以简化它，使mp1.player_id < mp2.player_id ……但“first”布尔值似乎有效）。

锦标赛ID和步骤在第二个表中重复出现，因为它们需要在该表的唯一索引上……以确保每个步骤中只有一个比赛。

以下是我的主要问题：

1.当前可能在第一张表（比赛）中存在孤立的行。一场比赛应该恰好有两名玩家。特别是，如果比赛存在于比赛表中，则可能在match_players表中没有与之匹配的行。有没有办法确保比赛始终在matches_players中具有两个关联行？通过“first”方法，我肯定限制了每场比赛的玩家人数少于2……因此找出一种确保最少2名玩家的方法将解决问题。

以下是我的一个担忧：

由于孤立的行仍然存在，是否还会出现其他数据异常？我对match_players中的复合（三重）主键有点不舒服，但我认为复合外键要求可以覆盖该表。

感谢任何能帮助我的人。这是我目前能做到的最好的。我认为如果解决了孤立的行问题，那么这个设计将是完美的。我想我可以设置一个cron作业来清除孤立的行，但在选择这个之前，我想知道是否存在更清晰的设计。

我确实认为，在检查约束中的子查询将解决此问题，但遗憾的是，我认为PostgreSQL尚未支持该功能。

- Dragontamer5788

---

如果你把真正的问题简化成国际象棋世界，你会得到关于国际象棋的答案。大多数这些答案可能对你没有用，它们可能会浪费每个人的时间。我在SO上的经验表明，如果你无法自己解决问题，也无法有效地简化它。相反，考虑发布DDL和样本INSERT语句以获取你真正的表格，并描述真正的问题。 - Mike Sherrill 'Cat Recall'

如果你正在建模象棋比赛，那么顺序很重要：白方先行，A对B与B对A不同（但是某个给定锦标赛的规则可能禁止两者都出现）。在你的模型中，似乎它们确实不同，因为你有第一个字段。如果你想避免第一个表中出现孤立的行，你可以简单地将包括两名玩家的比赛放在一行中（例如，第一个玩家ID，第二个玩家ID）。 - didierc

Answer 1

这是我所说的“前瞻性问题”，即您可能会遇到与尚未插入的行有关的数据限制问题。整个事务具有个别行不具备的要求。大多数数据库没有提供解决此问题的工具。幸运的是，PostgreSQL为您提供了一些选项。

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

第一种方法是向匹配添加一个类型为match_player[]的match列。然后在这里存储一个球员数组。这将使用触发器将其实现到match_player表中。但是，这样做在开发和预见角落情况方面存在重大惩罚。我认为这是一种可行的选择，但并非理想选择。这通过扁平化表来避免前瞻性约束。但是它只能存储步骤0记录。一旦人们开始移动...那必须仅通过插入到match_players来完成。

约束触发器

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

- Chris Travers