logo标签列表

MySQL创建一对一关系

mysqlsqldatabasedatabase-designrelational-database
3

我正在创建一个数据库，这是我第一次使用它们。

关于一对一的对应关系，我有一个问题。我正在创建一个代表某种运动队的表格。

我希望每个团队只有一个比赛场馆，并且每个团队都有自己的场馆。

场馆将有许多属性（名称、座位、门票价格等），因此，即使我已经阅读过最好的做法是把所有内容放在一个表格中，我仍然会为它们创建一个表格。

因为场馆和团队是两个如此不同的概念，所以我会分成两个表格，就像这样：

Club
--------------------
ClubID (PK)
ClubName
.
.
.
ArenaID (FK)


Arena
--------------------
ArenaID (PK)
ClubID (FK)
ArenaName
Seats
TicketPrice

此外，我想将ArenaID设为与ClubID相等，而非自动递增，因为当创建俱乐部时，我会自动创建竞技场。这样做合理吗？
我想要了解这种关系类型，因为它在其他情况下也会被使用（例如每个团队都将拥有一个助理教练及其技能）。
谢谢大家！
编辑：
@Kevin Bowersox 我只是在说明游戏的方式，感谢您的回答。
@Peter Gluck 您应将竞技场视为俱乐部的属性，但它有其他独立的属性，我希望将其与俱乐部表分开以更好地组织数据库。如果俱乐部想要“建造”另一个竞技场，它只需更改实际竞技场的名称，就可以更改座位数和票价。但“对象”保存在数据库中将保持不变。只有一些列会发生变化。
@Branko Dimitrijevic 感谢您详细的回答。为了更好地理解您所说的内容，我想问您一些问题（我现在开始使用数据库）。在第一个示例中，您没有为两个表使用两个键，这将分别成为主键和外键，而是使用一个既是主键又是外键的唯一键，这样做正确吗？这两种选择之间有什么区别？ 然后，您说如果我没有太多的团队，最好将所有内容放在单个表中，但是我希望会有成千上万的团队。这种情况下是否足够大的数量来拆分表格？我这样说是因为在这个表格中不仅有这一个一对一关系，还有其他的一对一关系，所以如果我将所有这些一对一关系都放在同一个表格中，它将非常非常大。
感谢大家的建议，现在我将尝试使用两个不同ID键的两个表的方法。但是在其他表格中，我将使用ypercube建议的方法！
如果球队无法去其他场馆，你怎么办才能有比赛呢？ - ceejayoz
@ceejayoz，1队对阵2队，确定主队并加入他们的竞技场。虽然问题没有完全表述清楚，但这样做是可行的。 - Kevin Bowersox
说实话，学习这个最好的方法就是去做，看看它是如何工作的，然后修复它，再看看它是如何工作的，反复进行。这真的是你获得经验的方式。数据库的结构取决于你想如何使用它和访问/处理数据。你现在可能有要求，但将来会怎样呢？记得要灵活思考，并确保使其符合你的舒适度和思维方式 - 特别是如果你将经常使用它。 - Peter Oram
是的，PK也是FK。显然，除非使用延迟约束（在MySQL下不受支持）打破循环，否则无法插入任何新数据。数十万个团队在现代硬件上仍然被视为“小型”，但是（像往常一样）在决定之前先进行测量...顺便说一句，我是在假设您确实需要1：1关系的情况下回答的。如果您需要1：0..1或0..1：0..1，则会改变整个方程式，其他答案已经提供了一些好的指导方向。 - Branko Dimitrijevic
4个回答
4

我喜欢将这些表拆分的想法。这将使您的对象模型运行得更好。

然而，关于：

此外，我认为应该将ArenaID设置为非自动增量，而是将其设置为ClubID，因为当创建俱乐部时，我将自动创建竞技场。这合理吗？

我建议保留ArenaID作为自己的自动整数。如果俱乐部更换竞技场会怎么样？这只是可能导致这些键不同步的情况之一。我认为在未来的时间内，您将使用这种方法手动调整其中一些键。保持您的关系数据库，仅仅是关系，并且不要依赖于这种情况。

无可挑剔的答案——基本上说了我想要的。 - Martin Bean
一个团队不能更改竞技场，因为它可以更改竞技场的名称和尺寸，所以将会有永久的一对一关系。竞技场只是决定你在主场比赛时收入的比赛，但它始终可编辑。 - Ikki
@Ikki 我并不反对一对一关系的概念，但我会非常谨慎地管理这些键。 - Kevin Bowersox
2
@Ikki 假设团队建了一个新的竞技场来比赛？假设他们想在新竞技场开放之前，在旧竞技场比赛时预售门票？无论它们之间的关系如何，本质上不同的对象应该有自己的键。 - Peter Gluck
1
在支持可延迟约束的数据库管理系统上，可以使用循环外键实现真正的“一对一”关系：
CREATE TABLE Club (
    ClubId INT PRIMARY KEY
    -- Other fields...
);

CREATE TABLE Arena (
    ClubId INT PRIMARY KEY REFERENCES Club (ClubId)
    -- Other fields...
);

ALTER TABLE Club ADD FOREIGN KEY (ClubId)
    REFERENCES Arena (ClubId)
    DEFERRABLE INITIALLY DEFERRED;

FK Club -> Arena 直到事务结束才会被执行，因此您可以在不遇到鸡生蛋问题的情况下插入Club和Arena。

很遗憾，在MySQL中，您将无法实现真正的“1对1”关系，因为它不支持延迟约束，因此在存在必要的循环FK的情况下插入新数据时，您将无法解决鸡和蛋问题。

您甚至不能真正依赖您提出的解决方案，因为MySQL不强制执行CHECK约束，因此您无法强制执行ClubId和ArenaId之间的相等性。可能最好的方法是依靠应用程序逻辑来执行此操作，从而使您面临潜在的错误。

使用备用键（即UNIQUE约束）也无济于事，因为：

CREATE TABLE Club (
    ClubId INT PRIMARY KEY,
    ArenaId INT UNIQUE
    -- Other fields...
);

CREATE TABLE Arena (
    ArenaId INT PRIMARY KEY,
    ClubId INT NOT NULL UNIQUE REFERENCES Club (ClubId)
    -- Other fields...
);

ALTER TABLE Club ADD FOREIGN KEY (ArenaId)
    REFERENCES Arena (ArenaId);

INSERT INTO Club (ClubId) VALUES (1);
INSERT INTO Club (ClubId) VALUES (2);
INSERT INTO Club (ClubId) VALUES (3);

INSERT INTO Arena (ArenaId, ClubId) VALUES (1, 2);
INSERT INTO Arena (ArenaId, ClubId) VALUES (2, 3);
INSERT INTO Arena (ArenaId, ClubId) VALUES (3, 1);

UPDATE Club SET ArenaId = 2 WHERE ClubId = 1;
UPDATE Club SET ArenaId = 3 WHERE ClubId = 2;
UPDATE Club SET ArenaId = 1 WHERE ClubId = 3;

现在你有一个俱乐部 (ClubId = 1)，它指向一个竞技场 (ArenaId = 2)，该竞技场又指向另一个俱乐部 (ClubId = 3)！等等...

即使它能够工作，聚集表中的二级索引也可能很昂贵，而所有InnoDB表都是聚集的。

您提出的“垂直分割”通常用于更好地利用缓存，当一个字段子集被查询的次数比另一个多得多且行数很大时。即使您有这种特殊的查询模式，我怀疑您是否有足够的团队遇到缓存问题。您应该重新考虑并将所有内容放在一个表中。
我已经在主问题中评论了你的帖子，你能回答我吗？因为你的评论非常有用，我想要更多的建议。 - Ikki
0

你在评论中说：

"一个团队不能更改竞技场，因为它可以更改竞技场的名称和尺寸，所以将会有永久的一对一关系。"

永远不要说永远或永久。需求是会变化的。

我同意@Peter Gluck的评论，"本质上不同的对象应该有自己的键，而不管它们之间的关系如何"

根据前两个观察结果，我会为所有三个对象/实体保留单独的表：俱乐部、竞技场和俱乐部在竞技场中比赛。您（当前）对俱乐部和竞技场之间的1-1关系的限制将通过两个唯一约束条件得到保留，如果需求发生变化，则可以解除这些约束条件。设计中不需要进行进一步的更改：

Club
--------------------
ClubID (PK)
ClubName
.
.

Arena
--------------------
ArenaID (PK)
ArenaName
Seats
.
.

ClubPlaysInArena
--------------------
ClubID  (PK, UQ1, FK1)
ArenaID (PK, UQ2, FK2)
.
.

奖励点：您可以在数据库中存储没有关联俱乐部的新建竞技场（以及没有竞技场的俱乐部）。
也许这是一个非常琐碎的问题，但我必须问一下你。为什么在ClubPlaysInArena表中将UQ属性分配给ID？不是唯一属性等于主键吗？ - Ikki
主键是(ClubID, ArenaID)。但有两个唯一约束，一个在(ClubID)上，另一个在(ArenaID)上。这些确保每个俱乐部最多只有一个关联的竞技场，每个竞技场最多只有一个关联的俱乐部。因此，俱乐部和竞技场具有1-1关系（更准确地说不是严格的1-1，而是0..1::0..1，因为在这个设计中可能会有没有竞技场的俱乐部和没有俱乐部的竞技场）。如果您（稍后）取消两个UQ约束之一，则关系变为1对多。如果您取消两个约束，则变为多对多。 - ypercubeᵀᴹ
因为ClubID和ArenaID在ClubPlaysInArena表中一起构成主键，所以我们必须将UK属性分配给它们，这个事实保证了COUPLE是唯一的。例如（如果C1、C2是两个ClubID，A1、A2是两个ArenaID），表中只能有一个(C1,A1)的组合，但是(C2,A1)和(C1,A2)这些组合可以存储在表中吗？ - Ikki
是的和不是。一旦您插入了(C1，A1)对，您就不能再添加(C1，任何内容)对（因为ClubID上的唯一约束，也不能再添加(任何内容，A1)对，因为ArenaID上的唯一约束。主键约束（在(ClubID，ArenaID)上）似乎是多余的（确实如此！），但如果您删除任何一个或两个唯一约束，它将不再是多余的。 - ypercubeᵀᴹ
0

您似乎很好地理解了这些概念。您确实在考虑适当的因素来推动您的决策。只要确保您所做的假设是正确的，并且将保持正确。（即，作为B队助理教练的个人永远不会被允许成为另一支球队的助理教练吗？如果所有关系的答案始终是肯定的，那么它们确实可以建模为一对一（事实上，您可以将它们全部作为单个团队表的属性而没有功能损失），但要小心，很少有这样的假设实际上是真实的，并且注定随着应用程序和数据模型的演变而保持真实...

假设这确实是一对一的情况，那么我考虑将它们拆分成单独的表的唯一原因是性能。例如，一个（较小）属性集每秒要访问数百次，而大部分属性相对稳定，访问频率要低得多。然后，您可以将更频繁访问的属性放入较小且较窄的表中，以获得更好的读写性能。仅出于逻辑或组织美学而分离属性并不是我会做的事情...

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接