为什么数据库设计中的m：n关系必须创建一个新关系？ [英] Why does the m: n relationship in the DB design have to create a new relation?

问题描述

我最近了解了数据库设计。但是，在m：n关系中，我们需要创建一个新关系。我不太清楚这个原因。
我也想知道为什么当我具有1：n关系时为什么要在n个关系中而不是1中放置一个外键。

I recently learned about DB design. However, in the m: n relation, we need to create a new relation. I do not understand this reason well. I also wonder why I place a foreign key in n relations instead of 1 when I have a 1: n relationship.

推荐答案

最初，Chen的将实体关系模型映射到关系模型的方法规定，每个关系映射到一个单独的关系（表）。但是，将一对一和一对多的关系非规范化为具有相同决定因素/关键字的实体关系已成为一种惯例，以限制数据库中的表数。网络数据模型思维的持续性（带有参考记录）也有助于推广这种方法。但是，并非所有关系关系都可以被非正规化-M：N二进制关系以及三元关系和更高关系具有包含多个实体集的键，因此必然需要它们自己的关系/表。

Originally, Chen's method for mapping the entity-relationship model to the relational model prescribed that every relationship map to a separate relation (table). However, it's become common practice to denormalize one-to-one and one-to-many relationship relations into entity relations with the same determinant/key, to limit the number of tables in a database. The persistence of network data model thinking (records with references) also helps to popularize this approach. However, not all relationship relations can be denormalized - M:N binary as well as ternary and higher relationships have keys that involve multiple entity sets, and so necessarily require their own relations/tables.

例如，考虑以下概念模型：

For example, consider the following conceptual models:

将每个实体关系和关系关系直接映射到单独的表将给出以下物理模型：

A direct mapping of each entity relation and relationship relation to separate tables would give the following physical models:

请注意，一对一关系 Manages 有两个候选键，但是物理模式要求我们选择一个作为主键。我选择 department_id ，因为将关系关系非规范化为 Employee 实体关系将需要可为空的列（因为并非每个 Employee 是经理）。

Note that the one-to-one relationship Manages has two candidate keys, but the physical schema requires that we choose one as primary key. I chose department_id since denormalization of the relationship relation into the Employee entity relation would require a nullable column (since not every Employee is a manager).

更重要的是，请注意N：1和1：1关系表具有与实体表之一相同的键。我们可以利用这一点来组合这些关系：

More importantly, note that the N:1 and 1:1 relationship tables have the same keys as one of the entity tables. We can take advantage of this to combine those relations:

但是，M：N和M：N：P关系具有组合键，并且不能与其他表组合（除非在较大的模型中，您巧合地在同一实体之间具有多个具有相同基数的关系）

However, the M:N and M:N:P relationships have composite keys, and can't be combined with other tables (unless, in a larger model, you coincidentally have multiple relationships with the same cardinalities between the same entity sets).

我还想知道为什么当我具有1：n关系时，将外键放在n关系中而不是1中。 / p>

I also wonder why I place a foreign key in n relations instead of 1 when I have a 1: n relationship.

正确地说，关系是由其独立实体集的组合确定的。这些集合的值可以出现多次，如M，N等所示。我们用1表示从属实体集-这些行为与属性类似，因为独立角色中的每个值的唯一组合都确定一个值每个依赖角色。这是功能依赖的逻辑概念。

It's more correct to say that a relationship is determined by the combination of its independent entity sets. Values of these sets can appear any number of times, as indicated by M, N, etc. We indicate dependent entity sets with a 1 - these act similar to attributes, in that each unique combination of values in the independent roles determines a single value for each dependent role. This is the logical concept of functional dependency.

当我们如上所述对表进行非规范化时，我们是基于匹配键进行的，这意味着只有依赖列从一个表转移到另一个。最后，看起来我们在关系的许多方面都向实体表添加了一个FK列，但实际上添加了从键到从属列的映射，而无需重复键。

When we denormalize tables as above, we do so based on matching keys, which means only dependent columns get transferred from one table to another. In the end, it may look like we added a single FK column to the entity table on the many side of the relationship, but we actually added a mapping from the key to the dependent column, and we just didn't need to repeat the key.

这篇关于为什么数据库设计中的m：n关系必须创建一个新关系？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！