“多到两个”关系 [英] 'Many to two' relationship
问题描述
一个真实世界的例子是电话号码，用户和公司。一个公司可以有很多电话号码，用户可以有很多电话号码，但是理想情况下，用户不应该提供与公司相同的电话号码，因为数据库中将会有重复的内容。
这是一个很好的问题。我不明白为什么有人把你打倒了（所以我标记你备份，因为我们都在这里学习）。无论如何，这个问题表明你不完全理解实体关系（不要粗鲁）。下面有四种（技术上只有3种）：
一对一
一对多
许多到一个
许多到许多
一对一（1：1）：
在这种情况下，为了符合规范化，或更通常的是开放式封闭原则，表格已分成两部分。
规范化合规性：您可能拥有每个客户只有一个帐户的业务规则。从技术上讲，在这种情况下，您可以说客户和帐户都可以在同一张表中，但是这会违反规范化规则，因此您可以将它们分开并进行1：1。
开放原则合规性：客户表可能有ID，第一个;姓氏和地址。后来有人决定添加一个出生日期，并与它一起计算年龄的能力以及一堆其他非常需要的领域。这是一对一的简化例子，但是主要用于扩展数据库而不会破坏现有的代码。写入的许多代码（可悲的是）紧密耦合到数据库，所以表的结构的变化将会破坏代码。添加这样的1：1将扩展表以满足新的要求，而不修改源代码，从而允许旧代码继续正常运行，新代码可以使用新的数据库功能。
以这种方式使用1：1关系的规范化和扩展表的缺点是性能。通常，对于大量使用的系统，提高数据库性能的第一个目标是将规则化并将这些表组合到单个表中，并优化索引，从而消除了使用连接和从多个表读取的需要。归一化/去规范化既不是一个好的或坏的，因为它取决于系统的需要。大多数系统通常在需要时开始正常化的更改，但是如上所述，如果代码紧密耦合到DB结构，则这种更改需要非常仔细地进行，几乎肯定会导致系统失败。即当您组合2个表时，一个不再存在，所有包含现在的非执行表的代码都将失败，直到它被修改为止（以db为单位，假设将关系连接到1：1中的任何表，当您删除这些表时）这破坏了关系，所以结构必须被大大修改以补偿。不幸的是，在大多数情况下，这样糟糕的设计在数据库世界中比软件世界更容易发现，你通常不会注意到发生错误在代码中，直到它都分崩离析），除非系统正确设计了分离关注
在面向对象编程中最接近的继承。但是它不一样。
一对多（1：M）/一对一（M：1）：
这两个关系（hense为什么4变成3），是最受欢迎的关系类型。他们是一样的关系，唯一改变的是你的观点。一个例子一个客户有很多电话号码，或者很多电话号码可以属于一个客户。
在面向对象编程中，这将被认为是组合。它不是继承，但你说的是一个项目是由许多部分组成的。这通常以类中的数组/列表/集合等表示，而不是继承结构。
多对多（M：M）：
这种类型的关系目前的技术是不可能的。因此，我们需要将它们与加入他们的关联表分解成两个一对多的关系。两个一对多的关系的许多方面总是在关联/链接表上。
对于你的例子，说你需要多对多的人是正确的。因为一个两到多是有效的（意思是多于一个）许多关系。这是让系统工作的唯一方法。除非您打算研究关系演算的领域，以找到一些新型的关系将允许这个。
对于这样的关系（m2m），您还有两个选择，可以在链接表中创建一个复合键，这样字段的组合就成为唯一的条目如果您对数据库优化感兴趣，这是较慢的选择，但占用的空间较小）。或者，您使用自动生成的ID列创建第三个字段，并将其设为主键（对于数据库优化，这是更快的选择，但占用更多空间）。
在你的具体例子中...
一个真实世界的例子是电话号码，用户和公司。一个公司可以有很多电话号码，用户可以有很多电话号码，但是理想情况下，用户不应该提供与公司相同的电话号码，因为数据库中将会有重复的内容。
这将是与电话号码表的许多关系，作为公司和用户之间的链接表。如上所述，为确保不再重复电话号码，您只需将其设置为主键或使用其他主键，并将电话号码字段设置为唯一。
对于那些一些问题，真的是你如何说出他们的话。什么导致你对此感到困惑，以及如何克服这种混乱，看到解决方案很简单。重新解决问题如下。开始询问是一对一，如果答案是否定的，请继续前进。下一个问题是一对多，如果答案是不动的。剩下的唯一其他选择是许多到许多。请小心谨慎，确保您在开始前仔细考虑了前两个问题。许多经验不足的数据库人员通常会通过定义一个到多个数目来复制问题。再次，迄今为止最受欢迎的一种关系是一对多（我会说90％），多对多，一对一分开了剩下的10％7/3。但这些数字只是我个人的观点，所以不要引用他们作为行业标准统计。我的观点是要额外加以肯定，绝对不是一对多选择多对多。这是值得的额外的努力。
所以现在找到两者之间的链接表，决定哪两个是你的主表，哪些字段需要在他们之间共享。在这种情况下，公司和用户表都需要共享电话。您需要制作一个新的手机表作为链接器。
一旦您决定3中的任何一个都不适合您，就会显示误解的警告闹铃。这应该足以告诉你，你根本没有正确地说出关系问题。随着时间的推移，你会越来越好，但这是一个基本的技能，真的应该尽快掌握自己的卫生。
当然你也可以转到面向对象的数据库，这将允许一系列称为Hierarchacal关系的其他关系。很好，如果你正在考虑编程程序员。但是我不会推荐这个，因为当你开始寻找结合各种类型的关系的方法时，会让你的头受伤。特别是因为没有太多的需求，因为世界上几乎所有的数据库都只包含这3种关系，除非它们是超级特权的东西。
希望这是一个合理的回答。感谢您抽出时间阅读。
I am wondering about a 'many to two' relationship. The child can be linked to either of two parents, but not both. Is there any way to reinforce this? Also I would like to prevent duplicate entries in the child.
A real world example would be phone numbers, users and companies. A company can have many phone numbers, a user can have many phone numbers, but ideally the user shouldn't provide the same phone number as the company as there would be duplicate content in the DB.
This is a nice question. I dont understand why someone has marked you down. (so I marked you back up, because we are all here to learn). Anyway, this question shows that you dont fully understand entity relationships (no rudeness intended). Of which there are four (technically only 3) types below:
One to One One to Many Many to One Many to Many
One to One (1:1): In this case a table has been broken up into two parts for purposes of complying with normalisation, or more usually the open closed principle.
Normalisation compliance: You might have a business rule that each customer has only one account. Technically, you could in this case say customer and account could all be in the same table, but this breaks the rules of normalisation, so you split them and make a 1:1.
Open-Close principle compliance: A customer table, might have id, first & last names, and address. Later someone decides to add a date of birth and with it the ability to calculate age along with a bunch of other much needed fields. This is an over simplified example of one to one, but you get the main use for it is to extend your database without breaking existing code. Much code written (sadly) is tightly coupled to the database so changes in the structure of a table will break the code. Adding a 1:1 like this will extend the table to meet new requirements without modifying the origional, thereby allowing old code to continue functioning normally and new code to make use of the new db features.
The downside of normalisation and extending tables using 1:1 relationships in this way is performance. Often times on heavly used systems, the first target to increase database performance is de-normalising and combining such tables into a single table, and optimising the indexes thus removing the need to use joins and read from multiple tables. Normalisation / De-Normalisation is neither a good or bad thing, as it depends on the needs of the system. Most systems usually start off normalised changing back when needed, but this change needs to be done very carefully as mentioned, if code is tightly coupled to the DB structure, it will almost definitely cause the system to fail. i.e. When you combine 2 tables, one ceases to exist, all the code that includes that now nonexistant table fails until it is modified (in db terms, imagine connecting relationships to any of the tables in the 1:1, when you remove those tables, this breaks the relationships, and so the structure has to be greatly modified to compensate. Unfortunately, such bad designs are much easier to spot in the DB world than in the software world in most cases and you don't usually notice something went wrong in code until it all falls apart) unless the system is properly designed with separation of concerns in mind.
It the closest thing you can get to inheritance in object oriented programming. But its not quite the same.
One to Many (1:M) / Many to One (M:1): These two relationships (hense why 4 become 3), are the most popular relationship types. They are both the same type of relationship, the only thing that changes is your point of view. An example A customer has many phone numbers, or alternately, many phone numbers can belong to a customer.
In object oriented programming this would be considered composition. Its not inheritance, but you are saying one item is composed of many parts. This is usually represented with arrays / lists / collections etc. inside of classes as opposed to an inheritance structure.
Many to Many (M:M): This type of relationship with current technology is impossible. For this reason we need to break it down into two one to many relationships with an "association" table joining them. The many side of the two one to many relationships is always on the association / link table.
For your example, the person who said you need a many to many is correct. Because a two to many is effectively a many (meaning more than one) to many relationship. This is the only way you would get your system to work. Unless you are intending to research the field of relational calculus to find some new type of relationship that would allow this.
Also for such relationships (m2m) you have two choices, either create a compound key in the linker table so the combination of fields become a unique entry (if you are interested in db optimisation this is the slower choice, but takes less space). Alternately, you create a third field with an auto generated id column and make that the primary key (for db optimisation, this is the faster choice, but takes more space).
In your example specifically above...
A real world example would be phone numbers, users and companies. A company can have many phone numbers, a user can have many phone numbers, but ideally the user shouldn't provide the same phone number as the company as there would be duplicate content in the DB.
This would be a many to many relationship with the phone number table as the linker table between companies and users. As explained, to ensure no phone number is repeated, you simply set it as the primary key or use another primary key and set the phone number field to unique.
For those kind of questions, it is really down to how you phrase them. What is causing you to get confused about this, and how you overcome this confusion to see the solution is simple. Rephrase the problem as follows. Start by asking is it a one to one, if the answer is no, move on. Next ask is it a one to many, if the answer is no move on. The only other option remaining is many to many. Be careful though, ensure you have considered the first 2 questions carefully before moving on. Many inexperienced database people often over complicate issues by defining one to many as many to many. Once again, the most popular type of relationship by far is one to many (I would say 90%) with the many to many and one to one spliting the remaining 10% 7/3 respectevely. But those figures are just my personal perspective, so dont go quoting them as industry standard statistics. My point is to make extra extra sure it is definitely not a one to many before choosing many to many. It is worth the extra effort.
So now to find the linker table between the two, decide which two are your main tables, and what fields need to be shared between them. In this case, company and user tables both need to share the phone. Hense you need to make a new phone table as the linker.
The warning alarm of misunderstanding should show as soon as you decide none of the 3 are working for you. This should be enough to tell you that you simply are not phrasing the relationship question correctly. You will get better at it as time passes, but it is an essential skill and really should be mastered as soon as possible for your own sanaty.
Of course you could also go to an object oriented database which will allow a range of other relationships called "Hierarchacal" relationships. Thats great if you are thinking of becomming a programmer too. But I wouldnt recommend this as it going to make your head hurt when you start finding ways to combine the various types of relationships. Especially given there is not much need since nearly all databases in the world consist of just those 3 types of relationships unless they are something super duper special.
Hope this was a reasonable answer. Thanks for taking the time to read it.
这篇关于“多到两个”关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
