用于约束强制配对的数据库设计 [英] Database design for constraint enforcing pairing

问题描述

如何最好地设计一个数据库，在那里我有一个玩家表（主键 player_id ），我想将它们组合成两个，以便数据库可以强制执行每个团队的约束由两个玩家组成，每个玩家最多在最多一个团队中。

How do I best design a database where I have one table of players (with primary key player_id) which I want to pair into teams of two so that the database can enforce the constraint that each team consists of exactly two players and each player is in at most one team?

我可以想到两个解决方案但是我不太高兴。

I can think of two solutions, but both I'm not too happy about.

一种可能性是有两列 player1_id 和 player2_id 那是指向播放器表格中 player_id 列的外键 外键。需要进行额外的检查，所以没有一个玩家同时是一个队伍的球员1和第二个队伍的player2。

One possibility is to have two columns player1_id and player2_id that are unique foreign keys pointing to the player_id column in the player table. An additional check is needed so that no player is at the same time player1 of one team and player2 of a second team.

我想到的另一种可能性是将玩家表和团队表与团队成员表连接，玩家表中的 player_id 列具有唯一外键，另一个外键指向主团队表的关键这里需要添加一个支票，每个团队都有两个成员。

The other possibility that comes to my mind is to connect the player table and the team table with a team membership table that has a unique foreign key to the player_id column in the player table and a second foreign key pointing to the primary key of the team table. Here a check has to be added that every team has exactly two members.

有没有更好的设计来简化对约束的检查？

Is there a better design that simplifies the checking of the constraints?

如果重要：我使用的数据库是PostgreSQL 8.4，我更喜欢其强大的规则系统尽可能地触发。

If it matters: the database I'm using is PostgreSQL 8.4 and I prefer its powerful rule system to triggers wherever possible.

编辑：基于AlexKuznetsov的答案的解决方案

A solution based on the answer of AlexKuznetsov

它对我来说并不完美，但我喜欢它比以前更好。我修改了Alex的解决方案，因为我不想让球员有一个外键，因为有一个应用阶段，玩家可以注册。

It doesn't feel perfect to me yet, but I like it much better than what I had before. I modified Alex' solution since I don't want to have a foreign key from players to teams, as there is an application phase where players can enroll.

create table TeamMemberships(
  player_id int not null unique references Players(player_id),
  team_id int not null references Teams(team_id),
  NumberInTeam int not null check(NumberInTeam in (0,1)),
  OtherNumberInTeam int not null, -- check(OtherNumberInTeam in (0,1)) is implied
  check(NumberInTeam + OtherNumberInTeam = 1)
  foreign key (team_id, OtherNumberInTeam) references TeamMemberships(team_id, NumberInTeam),
  primary key (team_id, NumberInTeam)
);

此定义确保团队成员身份成对（并将成对插入）。现在玩家最多可以有一个球队，球队可以有0个或完全是2个球员。为了确保每个团队都有成员，我可以在团队表中添加一个指向其两个会员资格的外键。但是像Erwin一样，我不是延迟约束检查的粉丝。任何想法如何改善与此相关？还是有一个完全不同的，更好的方法？

This definition makes sure that team memberships come in couples (and will be inserted pairwise). Now players can be in at most one team and teams can have exactly 0 or exactly 2 players. To ensure that each team has members I could add a foreign key in the team table that points to any of its two memberships. But like Erwin I'm not a fan of deferred constraint checking. Any ideas how to improve with respect to this? Or is there a completely different, better approach?

PS：该方法对于具有n> 2个玩家的团队也是如此。一个只需要用NextNumberInTeam替换AnotherNumberInTeam，其值（即约束）NumberInTeam + 1 mod n。

PS: The methods works also for teams with n>2 players. One just has to replace OtherNumberInTeam by NextNumberInTeam with the value (i.e. constraint) NumberInTeam+1 mod n.

推荐答案

我不知道这是否可以在Postgress上工作，但这里是SQL Server解决方案：

I don't know if this can work on Postgress, but here is a SQL Server solution:

CREATE TABLE dbo.Teams(TeamID INT NOT NULL PRIMARY KEY);
GO
CREATE TABLE dbo.Players(PlayerID INT NOT NULL PRIMARY KEY,
  TeamID INT NOT NULL FOREIGN KEY REFERENCES dbo.Teams(TeamID),
  NumberInTeam INT NOT NULL CHECK(NumberInTeam IN (1,2)),
  TeamMateID INT NOT NULL,
  TeamMatesNumberInTeam INT NOT NULL,
-- if NumberInTeam=1 then TeamMatesNumberInTeam must be 2
-- and vise versa
  CHECK(NumberInTeam+TeamMatesNumberInTeam = 3), 
  UNIQUE(TeamID, NumberInTeam),
  UNIQUE(PlayerID, TeamID, NumberInTeam),
  FOREIGN KEY(TeamMateID, TeamID, TeamMatesNumberInTeam)
    REFERENCES dbo.Players(PlayerID, TeamID, NumberInTeam)
);

INSERT INTO dbo.Teams(TeamID) SELECT 1 UNION ALL SELECT 2;
GO

- 您只能插入成对的玩家

-- you can only insert players in complete pairs

INSERT INTO dbo.Players(PlayerID, TeamID, NumberInTeam, TeamMateID, TeamMatesNumberInTeam)
SELECT 1,1,1,2,2 UNION ALL
SELECT 2,1,2,1,1;

您可以尝试插入单个播放器，或从团队中删除播放器，或插入两个以上每个团队的玩家 - 都会因为一整套限制而失败。

You can try inserting a single player, or deleting a player from a team, or inserting more than two players per team - all will fail due to a complete set of constraints.

注意：SQL Server中的做法是明确命名所有约束。我没有指定我的约束，以防万一与Postgres不兼容。

Note: the practice in SQL Server is to explicitly name all constraints. I did not name my constraints just in case that is not compatible with Postgres.

这篇关于用于约束强制配对的数据库设计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！