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

2022-01-09 00:00:00 database insert mysql relational-database database-design

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

我的意思是，在 MySQL 5.5 和 InnoDB 表中，我的数据库设计类似于以下

I mean, in a MySQL 5.5 and tables InnoDB, I have a database design similar to the following

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

The problem arises when we try to insert rows in table1 and table2. Since there is no multi-table insert in MySQL, I can not insert a row becouse the foreign keys are NOT NULL fields in both tables and should be inserted simultaneously in both.

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

Which is the bes way to solve this problem?

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

I have in mind 3 possible solutions, but I want to know if there are more than these or which is the best and why.

  1. 设置外键字段为NULLABLE，在表中插入一行后，插入另一行，然后更新第一个.

  1. Set the foreign key field as NULLABLE and after insert one row in a table, insert the other one and afterwards, update de first one.

如上所示，但带有一个特殊的值，例如 -1.首先，在一个表中插入一个外部 key = -1 相当于 NULL 但避免将该字段设置为 NULLABLE.之后，我们在另一个表中插入该行并更新第一个插入的行.

Just as indicated above but with an special value like -1. First, insert in one table with foreign key = -1 that is equivalent to NULL but avoiding set the field as NULLABLE. Afterwards, we insert the row in the other table and update the first one inserted.

在两者之间创建一个关系表，虽然这并不是真正必要的，因为它是 1 比 1 的比例

Create a relational table between both though it is not really necessary because it is a 1 to 1 ratio

谢谢！！

编辑我简要解释一下我需要这种循环关系:它是从父表到其子表的非规范化.始终具有父表中排名最高的子表的参考是为了提高性能而制定的.

EDIT I briefly explain what I need this circular relationship: It is a denormalization from the parent table to one of its childs. It is made in order of high performance to have always the reference of the best ranked child from a parent table.

推荐答案

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

I'll make this an answer as I feel this is a design flaw.

首先，如果这两个表是真正的1:1关系，那你为什么不只有一个表呢?

First, if the two tables are in true 1:1 relationship, why don't you just have one table?

其次，如果不是真正的 1:1 关系而是超类型-子类型问题，您也不需要这个循环外键.假设 table1 是 Employee 而 table2 是 Customer.当然，大多数客户不是员工(反之亦然).但有时客户也可能是员工.这可以通过 3 个表来解决:

Second, if it's not a true 1:1 relationship but a supertype-subtype problem, you don't need this circular foreign keys either. Lets say table1 is Employee and table2 is Customer. Off course most customers are not employees (and vice-versa). But sometimes a customer may be an employee too. This can be solved having 3 tables:

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 关系.然后，您想以某种方式为每个父母存储表现最好(基于定义的计算)的孩子.

In the scenario you describe you have two tables Parent and Child having 1:N relationship. Then, you want to store somehow the best performing (based on a defined calculation) child for every parent.

这行得通吗?:

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)并且引用中没有循环路径.对最佳孩子的引用存储在额外表中，而不是 Parent 表中.

This way, you enforce the wanted referential integrity (every BestChild is a Child, every Parent has only one BestChild) and there is no circular path in the References. The reference to the best child is stored in the extra table and not in the Parent table.

您可以通过加入为每位家长找到 BestChild:

You can find BestChild for every Parent by joining:

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

<小时>

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

Additionally, if you want to store best children for multiple performance tests (for different types of tests, or tests in various dates), you can add a test field, and alter the Primary Key to (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)

相关文章