我已经仔细考虑过这个问题,但是没有想到更好的解决方案。所以让我描述一下我的问题、目前的解决方案以及我想要改进的地方。我还有一些担忧,比如我的设计是否真正规范化等。
我正在制作一个数据库,用于存储锦标赛中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尚未支持该功能。