MySQL 数据库设计。在 1to1 表中插入行。

admin

MySQL 数据库设计。在 1to1 表中插入行。

sql

将行插入到相互引用 1 到 1 的表中的最佳方法是什么？

我的意思是，在 MySQL 5.5 和表 InnoDB 中，我有一个类似于以下的数据库设计在此处输入图片说明

当我们尝试在 table1 和 table2 中插入行时出现问题。由于 MySQL 中没有多表插入，我不能插入一行，因为两个表中的外键都是 NOT NULL 字段，应该同时插入。

解决这个问题的最佳方法是什么？

我想到了 3 种可能的解决方案，但我想知道是否有更多的解决方案，或者哪个是最好的以及为什么。

将外键字段设置为NULLABLE，在表中插入一行后，插入另一行，然后更新第一个。
正如上面所指出的，但有一个特殊的值，比如 -1。首先，将与key = -1NULL 等效的外来表插入到一个表中，但避免将该字段设置为 NULLABLE。之后，我们在另一个表中插入该行并更新插入的第一个。
在两者之间创建一个关系表，虽然它不是真正必要的，因为它是 1 比 1 的比例

谢谢！！

编辑我简要解释了我需要这种循环关系的内容：它是从父表到其子表之一的非规范化。为了获得高性能，始终具有父表中排名最高的子项的引用。

阅读 268

2021-07-01

共1个答案

admin

我会回答这个问题，因为我觉得这是一个设计缺陷。

首先，如果两张表是真正的1:1关系，为什么不只有一张表呢？

其次，如果它不是一个真正的1:1关系而是一个超类型-子类型问题，你也不需要这个循环外键。让我们说table1是Employee和table2是Customer。当然，大多数客户不是员工（反之亦然）。但有时客户也可能是员工。这可以通过 3 个表来解决：

Person
------
id
PRIMARY KEY: id

Employee
--------
personid
lastname
firstname
... other data
PRIMARY KEY: personid
FOREIGN KEY: personid
    REFERENCES Person(id)

Customer
--------
personid
creditCardNumber
... other data
PRIMARY KEY: personid
FOREIGN KEY: personid
    REFERENCES Person(id)

在您描述的场景中，您有两个表Parent并且Child有1:N关系。然后，您希望以某种方式为每个父级存储表现最佳（基于定义的计算）的子级。

这行得通吗？：

Parent
------
id
PRIMARY KEY: id

Child
-----
id
parentid
... other data
PRIMARY KEY: id
FOREIGN KEY: parentid
    REFERENCES Parent(id)
UNIQUE KEY: (id, parentid)             --- needed for the FK below

BestChild
---------
parentid
childid
... other data
PRIMARY KEY: parentid
FOREIGN KEY: (childid, parentid)
    REFERENCES Child(id, parentid)

通过这种方式，您可以强制执行所需的参照完整性（每个 BestChild 都是一个 Child，每个 Parent 只有一个 BestChild）并且 References 中没有循环路径。对最佳孩子的引用存储在额外的表中，而不是存储在Parent表中。

您可以通过以下方式为每位家长找到 BestChild：

Parent
  JOIN BestChild
    ON Parent.id = BestChild.parentid
  JOIN Child
    ON BestChild.childid = Child.id

此外，如果您想为多个性能测试（针对不同类型的测试或不同日期的测试）存储最佳子项，您可以添加一个test字段，并将主键更改为(test, parentid)：

BestChild
---------
testid
parentid
childid
... other data
PRIMARY KEY: (testid, parentid)
FOREIGN KEY: (childid, parentid)
    REFERENCES Child(id, parentid)
FOREIGN KEY: testid
    REFERENCES Test(id)

2021-07-01