识别关系 - 许多 [英] Identifying relationship - many to many

问题描述

我为一个托儿中心建立了一个小型数据库。我一直在阅读识别和非识别关系，仍然有点困惑的整个事情。我已经使用MySQL Workbench来设计数据库。

现在，父节点和子节点的标准是 Parent 可以有一个或多个子，反之亦然 - ergo，父母和儿童是一个多对多。解决这个问题的最好办法就是创建第三个表 - Parets_Children ，并将其用作其他两个表之间的连接：

 家长 -  1：n  -  Parents_Children n：1  - 儿童
  

现在我的问题是在这里使用识别关系。逻辑是，没有父母没有孩子可以存在，父母不会是日托的成员，除非他们有至少一个孩子。他们都不会单独存在。但是，当让MysQL WB为我排序时，它会创建一个非标识关系（我认为）。

非常简化的代码：

  CREATE TABLE'parents'（
'id_parents'SMALLINT UNSIGNED NOT NULL，
'name'VARCHAR（48）NOT NULL，
 PRIMARY KEY '）; 
 
 CREATE TABLE'parents_children'（
'id_parents'SMALLINT UNSIGNED NOT NULL，
'id_children'SMALLINT UNSIGNED NOT NULL，
 PRIMARY KEY '，'id_children'），
 FOREIGN KEY（'id_parents'）REFERENCES'parents'（'id_parents'）
 FOREIGN KEY（'id_children'）REFERENCES'children'（'id_children'）; 
 
 CREATE TABLE'children'（
'id_children'SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT，
'name'VARCHAR（48）NOT NULL，
 PRIMARY KEY（'id_children'） ; 
  

这个输出是否足以达到所需的功能？我误解了整个关系的事情吗？希望我的问题不是太obsqure。

解决方案

从您提供的模式，我可以看到外键约束存在表 parents_children ，这将确保父级和子级之间的链接只有父级和子级都存在才能存在。

然而，这不会阻止您在 parents 中插入条目，而不在 parents_children 中附加条目， code> children 。

简而言之，此模式允许父元素存在，

这里的逻辑的问题是父和子需要在 parent_children 中创建关系之前创建。 （由于外键关系）。但是，您要查找的附加逻辑（没有子对象没有父对象，反之亦然）将需要父对象和子对象之间的所有链接，才能创建父对象或子对象。

您在这里看到鸡/鸡蛋问题吗？

I have made a small database for a daycare centre. I have been reading up on identifying and non-identifying relationships and am still a bit confused about the whole thing. I have been using MySQL Workbench to design the database.

Now, the criterias for parents and children are that a Parent can have one or more Children and vice versa - ergo, the relationship between Parents and Children is a many-to-many. The best praktice to solve this (as I understand) is to make a third table - Parets_Children and use that as a "connection" between the other two:

Parents - 1:n - Parents_Children n:1 - Children

Now the issue I have is wether or not to use an identifying relationship here. The logic is that no child can exist without a parent and the parents wouldn't be members of the daycare unless they had at least one child there. Neither of them would exist "alone". However, when letting MysQL WB sorting it out for me, it creates a non-identifying relationship (I think).
Very simplified code:

CREATE TABLE 'parents' (
  'id_parents' SMALLINT UNSIGNED NOT NULL ,
  'name' VARCHAR(48) NOT NULL ,
  PRIMARY KEY ('id_parents');

CREATE TABLE 'parents_children' (
  'id_parents' SMALLINT UNSIGNED NOT NULL ,
  'id_children' SMALLINT UNSIGNED NOT NULL ,
  PRIMARY KEY ('id_parents', 'id_children') ,
  FOREIGN KEY ('id_parents') REFERENCES 'parents' ('id_parents' )
  FOREIGN KEY ('id_children' ) REFERENCES 'children' ('id_children' );

CREATE TABLE 'children' (
  'id_children' SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT ,
  'name' VARCHAR(48) NOT NULL ,
  PRIMARY KEY ('id_children');

Would this output be sufficient to reach the desired function? Have I misunderstood the whole relationship thing? I hope my question isn't too obsqure.

解决方案

From the schema you provided I can see that the Foreign Key Constraint exists on table parents_children, which will ensure that the link between parent and child can only exist if both parent and child exists.

This does not however stop you from inserting an entry into parents, without an accompanying entry in parents_children or for that matter children.

So in short, this schema allows for a parent to exist, without a child.

The problem with your logic here would be that both the parent and child needs to be created before the relationship can be created in parents_children (due to the foreign key relationships). However, the additional logic that you are looking for (no child no parent and vice versa) would require all a link between parent and child before a parent or child can be created.

You see the chicken/egg problem here?

这篇关于识别关系 - 许多的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！