MySQL数据库设计。在1to1表中插入行。 [英] MySQL Database design. Inserting rows in 1to1 tables.

问题描述

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

5.5和表InnoDB，我有一个类似下面的数据库设计

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

这是解决这个问题的方法？

我想到3个可能的解决方案，但我想知道是否有更多的这些或哪个是最好的

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




2. 正如上面指出的，但是有一个特殊的值，如-1。首先，在一个具有外部 key = -1 的表中插入等效于NULL，但是避免将字段设置为NULLABLE。




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

EDIT
我简要解释一下我需要这个循环关系：它是一个从父表到其子表之一的反规范化。

解决方案

我的父级表中的最佳排名子项的引用。我会让这个答案，因为我觉得这是一个设计缺陷。





首先，如果两个表都是真实的 1：1 关系，





---





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

 人
 ------ 
 id 
 PRIMARY KEY：id 
 
员工
 -------- 
 personid 
 lastname 
 firstname 
 ...其他data 
 PRIMARY KEY：personid 
 FOREIGN KEY：personid 
参考人（id）
 
客户
 -------- 
 personid 
 creditCardNumber 
 ...其他数据
主键：personid 
外键：personid 
参考人（id）
  





---





在您描述的方案中，您有两个表 Parent 和 Child 有 1：N 关系。





这是否可以工作？：

 父
 ------ 
 id 
主键：id 
 
子
 ----- 
 id 
 parentid 
 ...其他数据
 PRIMARY KEY：id 
 FOREIGN KEY：parentid 
 REFERENCES Parent id）
 UNIQUE KEY：（id，parentid）--- FK下面需要的
 
 BestChild 
 --------- 
 parentid 
 childid 
 ...其他数据
 PRIMARY KEY：parentid 
 FOREIGN KEY：（childid，parentid）
 REFERENCES孩子（id，parentid）
  

这样，你强制实现所需的引用完整性（每个BestChild是一个Child，每个Parent只有一个BestChild）循环路径。对最好的孩子的引用存储在额外表中，而不是在 Parent 表中。





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

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





---





此外，如果你想存储最好的孩子进行多种性能测试（不同类型的测试，日期），可以添加测试字段，并将主键更改为（test，parentid）：





  BestChild 
 --------- 
 testid 
 parentid 
 childid 
 ...其他数据
 PRIMARY KEY：（testid，parentid）
 FOREIGN KEY：（childid，parentid）
 REFERENCES孩子（id，parentid）
 FOREIGN KEY：testid 
 REFERENCES测试（id）
  




What is the best way to insert rows into tables with references 1 to 1 of each other?

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

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?

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. 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.

2. 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.

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

Thanks!!

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.

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

---

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)

---

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.

Would this work?:

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)

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.

You can find BestChild for every Parent by joining:

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

---

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)

这篇关于MySQL数据库设计。在1to1表中插入行。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！