代码之家 › 专栏 › 技术社区 › j.p.

约束强制配对的数据库设计

postgresql database-design sql

-1

j.p. · 技术社区 · 16 年前

如何最好地设计一个数据库,其中有一个玩家表(带主键) 游戏者身份证 )我想将其配对成两个组,这样数据库就可以强制执行每个组所包含的约束。 恰好两 玩家和每个玩家都在至多团队?

我可以想出两个解决方案,但这两个我都不太满意。

一种可能是有两列 玩家1ID 和 玩家2 那是 独特的 指向的外键 游戏者身份证 播放机表中的列。需要进行额外的检查,以便一个队的球员1和另一个队的球员2不能同时在场。

我想到的另一种可能性是将玩家表和团队表与具有 独特的 的外键 游戏者身份证 玩家表中的列和指向团队表的主键的第二个外键。这里必须加上一个检查,每个团队只有两个成员。

有没有更好的设计可以简化约束的检查?

如果重要的话:我使用的数据库是PostgreSQL 8.4,我更喜欢它的强大功能 rule system 尽可能触发。

编辑:基于Alexkuznetsov答案的解决方案

它对我来说还不完美,但我比以前更喜欢它。我修改了亚历克斯的解决方案,因为我不想拥有从球员到球队的外键,因为有一个应用程序阶段可以让球员注册。

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个玩家。为了确保每个团队都有成员,我可以在团队表中添加一个指向其任何两个成员身份的外键。但和欧文一样,我不喜欢延迟约束检查。对此有什么改进的想法吗?还是有一种完全不同的更好的方法?

PS:这种方法也适用于有N>2名球员的球队。只需用值(即约束)numberinteam+1 mod n将othernumberinteam替换为nextNumberinteam。

4 回复 | 直到 16 年前

A-K 16 年前

我不知道这是否适用于Postgres,但这里有一个SQL Server解决方案:

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

--只能将玩家成对插入

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

您可以尝试插入一个玩家,或从一个团队中删除一个玩家,或在每个团队中插入两个以上的玩家-由于一组完整的约束,所有这些都将失败。

注意:SQL Server中的实践是显式命名所有约束。我没有命名我的约束,只是为了防止它与Postgres不兼容。

Dave 16 年前

这听起来像是在用户界面中比在数据库中更容易执行的操作。

将玩家添加到团队时,不允许添加超过2名玩家,只允许添加尚未加入团队的玩家。

HLGEM 16 年前

第一个解决方案是有限的,如果有必要改变为有三个成员的团队。

我喜欢teammemebrs表的概念,但是为了强制您的约束,您将永远不能一次只插入一个记录。你所有的插页必须是两个一组。此外,当A队的球员A IA希望他被转移到B队时,你要做的是什么?现在你必须找个人转移到A队,然后把他和他的搭档添加到B队。

如果团队只有两个成员,并且每个成员都只在一个团队中,那么创建和填充团队并使其处于活动状态会更好吗?至少可以一次完成一个更改。SP您可以将人员A从团队A移动到团队B,并使团队A处于非活动状态,直到找到要添加的其他人员为止。

Erwin Smout 16 年前

在我看来,通过player1 id player2_id设计,您可以在向工作组表插入一次后检查所有约束。

在团队+团队成员设计中,您将进入延迟约束检查的丑陋,尤其是您必须设置的所有触发/规则。

顺便说一句:类似sira-uprise这样的系统是以明确的目的构建的,目的是纯声明地精确地处理这些类型的约束强制问题,即没有任何触发器麻烦,也没有涉及任何其他形式的编程。你可能会感兴趣。