One of the most important things in databases is to understand the types of relations in the databases. That stands for both – a process of designing a database model as well as when you’re analyzing your data. Understanding these relations is somehow natural and not so complex but is still essential in the database theory (and practice).
数据库中最重要的事情之一就是了解数据库中关系的类型。 这既代表设计数据库模型的过程，又代表分析数据的过程。 对这些关系的理解在某种程度上是自然的，并不是那么复杂，但是在数据库理论（和实践）中仍然是必不可少的。
资料模型 (Data model)
In the previous article, Learn SQL: SQL Scripts, we’ve extended the data model we’ve used so far by adding a few new tables and filling them with the data. “Very nice. I like.”. That is the model in the picture below:
在上一篇文章“ 学习SQL：SQL脚本”中 ，我们通过添加一些新表并用数据填充它们，扩展了到目前为止使用的数据模型。 “非常好。 我喜欢。” 。 这就是下图中的模型：
While the model itself has only 6 tables (real-life models could have hundreds of tables), it contains the most common rules we’ll meet in many other models as well. This also stands for types of relations between tables. Without much effort, you can easily notice that each table is connected/related to another table with exactly one line (foreign key). The primary key from one table (e.g. employee.id) is related to the value from another table (e.g. call.employee_id). We’ll use this relation when we need data from both these tables, mostly when we’re writing select queries. The question remains on how to relate two tables. Even between two lines (relations), we could have some minor differences. We’ll discuss that now.
尽管模型本身只有6个表（现实生活中的模型可能有数百个表），但它包含我们在许多其他模型中也会遇到的最常见的规则。 这也代表表之间关系的类型。 无需花费太多精力，您就可以轻松地注意到每个表都只用一行（ 外键 ）连接到/关联到另一个表。 一个表（例如，employee.id）中的主键与另一表（例如，call.employee_id）中的值相关。 当我们需要这两个表中的数据时（主要是在编写select查询时），我们将使用此关系。 问题仍然在于如何关联两个表。 即使在两行（关系）之间，我们也可能会有一些细微的差异。 我们现在讨论。
关系类型 (Types of relations)
There are 3 different types of relations in the database:
数据库中存在3种不同类型的关系：
- one-to-one 一对一
- one-to-many, and 一对多
- many-to-many 多对多
Although they are different, they are represented in (almost) the same manner in the databases and that is the line between the two tables. So, what’s different? We’ll explain each of these relations types separately and comment on what is their actual purpose.
尽管它们不同，但是在数据库中（几乎）以相同的方式表示它们，这就是两个表之间的界线。 那么，有什么不同呢？ 我们将分别解释每种关系类型，并评论它们的实际目的。
一对多关系 (One-to-many relation)
The first of our 3 types of relations, we’ll start with is one-to-many. The reason for that is that it’s the most commonly used and the remaining two are “subtypes” of this one. Let’s start with a real-life problem.
我们从三种关系中的第一种开始，是一对多的。 原因是它是最常用的，其余两个是该类型的“子类型”。 让我们从一个现实问题开始。
例 (Example)
Imagine that we want to store a list of all our customers in the database. For each customer, we also want to store the city where this customer is located, and we know that the customer will be in exactly one city.
假设我们要在数据库中存储所有客户的列表。 对于每个客户，我们还希望存储该客户所在的城市，并且我们知道该客户将恰好在一个城市中。
This the typical example of one-to-many relation and this is how we solved it in our model:
这是一对多关系的典型示例，这就是我们在模型中解决它的方式：
We simply established a relation from the city.id to customer.city_id. And this works, because the customer can be only in one city and the city could have many different customers located in it.
我们只是简单地建立了从city.id到customer.city_id的关系。 这行得通，因为客户只能在一个城市中，并且该城市中可能有许多不同的客户。
When you want to determine the nature of the relation you need to establish between two tables just do this. In our example – For one city, we could have many different customers located in it. And the other way around – For one customer, we can have only one city it’s located in.
当您想要确定关系的性质时，只需在两个表之间建立关系即可。 在我们的示例中–对于一个城市，我们可以在其中拥有许多不同的客户。 反之亦然–对于一个客户，我们只能拥有一个城市。
So, how to choose between these 3 different types of relations? If you said the word “many” only once, then this is one-to-many relation. If you would use the word “many” two times, the relation would be many-to-many. And if you wouldn’t use it at all, then it would be one-to-one.
那么，如何在这三种不同类型的关系之间进行选择？ 如果您只说过“很多”一词，则这是一对多的关系。 如果您两次使用“许多”一词，则该关系将是多对多的。 而且，如果您根本不使用它，那将是一对一的。
- Tip:提示： One-to-many relation is resolved in such a manner that you add an attribute to the table that “is related to” word “many” and establish the relationship between this attribute and id of the original table. 一对多关系的解决方式是，向表中添加一个与“很多”一词相关的属性，并在该属性和原始表的ID之间建立关系。
In our case, we’ve added city_id to the customer table and related it to city.id attribute.
在本例中，我们已经将city_id添加到客户表并将其与city.id属性相关联 。
Now, let’s write 2 simple SQL SELECT statements and check if this is really true:
现在，让我们编写2个简单SQL SELECT语句，并检查这是否确实是真的：
SELECT *
FROM city;
SELECT *
FROM customer;
We can easily notice few things:
我们可以很容易地注意到一些事情：
- Not all cites were used (only these with ids 1, 3 and 4 were) 并非所有引用都被使用（仅ID为1、3和4的引用被使用）
- customer.city_id) customer.city_id ）
Now, we’ll write 3 more queries and join these two tables. Queries are:
现在，我们将再编写3个查询并将这两个表连接起来。 查询是：
SELECT *
FROM customer
INNER JOIN city ON customer.city_id = city.id;
SELECT *
FROM customer
LEFT JOIN city ON customer.city_id = city.id;
SELECT *
FROM city
LEFT JOIN customer ON customer.city_id = city.id;
The results returned are in the picture below:
返回的结果如下图所示：
Let’s shortly comment on these results.
让我们简短地评论一下这些结果。
The first query (using INNER JOIN) returned only rows where cities and customers had a pair. Since we had 4 rows for customers and all 4 had related city defined, the final result also has 4 rows.
第一个查询（使用INNER JOIN）仅返回城市和客户有一对的行。 由于我们为客户提供了4行，并且所有4行都定义了相关城市，因此最终结果也有4行。
The second query (customer LEFT JOIN city) returned the same result as the first one. That happened because all customers had related city defined. In case some customers wouldn’t have city_id defined (NULL), these customers would be included in this result too.
第二个查询（客户LEFT JOIN城市）返回的结果与第一个查询相同。 发生这种情况是因为所有客户都定义了相关的城市。 如果某些客户未定义city_id （NULL），则这些客户也将包含在此结果中。
The last query (city LEFT JOIN customer) returns more rows than the previous two queries. It returns all 4 rows they’ve returned, but also returns 3 more rows for cities where we have no customers. And that’s completely ok because if we wrote query this way, we obviously wanted to point to that fact.
最后一个查询（城市LEFT JOIN客户）返回的行比前两个查询多。 它会返回他们已返回的所有4行，但对于没有客户的城市，还会返回3行。 这完全可以，因为如果我们以这种方式编写查询，那么我们显然想指出这一事实。
多对多关系 (Many-to-many relation)
The second out of three types of relations is a many-to-many type. This type is used when both tables could have multiple rows on the other side. Let’s see an example.
三种关系中的第二种是多对多类型。 当两个表的另一侧可能有多行时，将使用此类型。 让我们来看一个例子。
例 (Example)
We need to store calls between employees and customers.
我们需要在员工和客户之间存储呼叫。
One employee, during the time, could call many customers. Also, one customer, during the time, could receive calls from many employees.
在此期间， 一名员工可以致电许多客户。 同样，在此期间， 一位客户可以接听许多员工的电话。
Notice that we’ve mentioned the word “many” two times. This is the signal we need to resolve this using many-to-many relation (out of 3 types of relations we have on disposal). To solve it we’ll:
请注意，我们已经两次提到“很多”这个词。 这是我们需要使用多对多关系（在我们要处理的3种关系中）来解决此问题的信号。 为了解决这个问题，我们将：
- employee and 员工和customer 客户之间添加表
- employee_id & employee_id和customer_id) to that new table (customer_id ）添加到该新表中（ call) 调用 ）
Now, when we look from the employee perspective, one employee could make many (multiple) calls. On the other hand, one customer could be related to many (multiple) calls. Therefore, many-to-many relation is implemented with adding a new table and one-to-many relations from both sides.
现在，从员工的角度来看， 一个员工可以拨打许多电话。 另一方面， 一个客户可能与许多 （多个）呼叫相关。 因此，通过添加新表和双方的一对多关系来实现多对多关系。
Let’s peek into the contents of these three tables now. We’ll use simple queries:
现在让我们窥视这三个表的内容。 我们将使用简单的查询：
SELECT *
FROM employee;
SELECT *
FROM call;
SELECT *
FROM customer;
The result is in the picture below:
结果如下图所示：
You can easily notice that the table call has attributes employee_id related to the employee.id and customer_id related to the customer.id. Since they are foreign keys, they hold only values from the set defined in the referenced tables (employee & customer).
您可以轻松地注意到，表调用具有与employee.id相关的employee_id和与customer.id相关的customer_id属性。 由于它们是外键，因此它们仅保存引用表（ 员工和客户 ）中定义的集合中的值。
Actually, we have 1 more foreign key here and that is the call.call_outcome_id. The relation between tables call and call_outcome is one-to-many. This means that the table call actually relates three tables – customer, employee, and call_outcome.
实际上，我们这里还有1个外键，即call.call_outcome_id 。 表call和call_outcome之间的关系是一对多的。 这意味着表调用实际上与三个表相关联： customer ， employee和call_outcome 。
The attribute call.call_outcome_id could contain NULL value (e.g. when the call starts, we still don’t know the outcome and it shall be defined later). That is the reason why, on the relation line, close to the table call_outcome, you can see a little circle (representing “zero”). Other one-to-many relations have a vertical line (representing “one”).
属性call.call_outcome_id可以包含NULL值（例如，当调用开始时，我们仍然不知道结果，稍后将对其进行定义）。 这就是为什么在关系线上靠近表call_outcome的地方 ，您会看到一个小圆圈（表示“零”）的原因。 其他一对多关系有一条垂直线（代表“一个”）。
一对一关系 (One-to-one relation)
Compared to previously mentioned types of relations, this one is really rarely used. Let’s go with an example.
与前面提到的关系类型相比，这种关系确实很少使用。 让我们来看一个例子。
例 (Example)
In the database, we want to store employees, but also their valid identity cards. We’re not interested in storing any other types of documents or identity cards that were previously valid, so we need exactly 1 (or none) identity card for 1 employee.
在数据库中，我们要存储员工，还要存储他们的有效身份证。 我们不希望存储以前有效的任何其他类型的文档或身份证，因此我们只需要为1名员工提供1张（或完全没有）身份证。
Let’s check this truly is a one-to-one relation. We’ve been given these rules: One employee could have only one valid identity card in our system. One identity card could belong to only one employee. We haven’t used the word “many”, so this can’t be any type of relation including the word “many”.
让我们检查一下这确实是一对一的关系。 我们得到了以下规则： 一名员工在我们的系统中只能拥有一张有效的身份证。 一张身份证只能属于一名雇员。 我们没有使用“很多”一词，因此这不能是任何类型的关系，包括“很多”一词。
We could do two things here:
我们可以在这里做两件事：
- employee table. This is how it’s usually done and the reason for doing it differently (as mentioned below) is some kind of exception 员工表中。 这是通常的做法，而这样做的原因（如下所述）是某种例外
- identity_card.employee_id), referencing employee.id的外键（ employee.id, should, at the same time, be the primary key of the identity_card.employee_id ）应该是identity_card table. This way we could have only 1 record per employee identity_card表的主键。 这样我们每个员工只能有1条记录
We could decide to go with the second option if we want:
如果需要，我们可以决定采用第二种选择：
- To keep identity card data separately because we want to keep the model clear and follow the same logic in the whole model (each entity from the real-world has its’ own table in the data model) 由于我们要保持模型清晰并在整个模型中遵循相同的逻辑，因此要分开保存身份证数据（现实世界中的每个实体在数据模型中都有自己的表）
- Maybe not all employees will have identity cards, so we’ll spare some storage space this way 也许不是所有的员工都有身份证，所以我们将以此方式节省一些存储空间
Please notice that one-to-one was also implemented in the same manner as one-to-many (1 relation) but with the additional condition (the foreign key is also the primary key).
请注意，一对一也以与一对多（1关系）相同的方式实现，但有附加条件（外键也是主键）。
结论 (Conclusion)
Today, we’ve described 3 types of relations used in the databases. While this is more of a theory, it’s necessary for understanding how everything works. In upcoming articles, especially those focused on how to write (complex) SQL queries, I’ll also, from time to time, mention these 3 types.
今天，我们已经描述了数据库中使用的3种关系类型。 尽管这更多是一种理论，但了解所有事物的工作方式是必要的。 在即将发布的文章中，尤其是那些专注于如何编写（复杂）SQL查询的文章中，我还将不时提及这3种类型。
738
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
