Ms Access 简明教程

MS Access - Relating Data

在本章中，我们将了解有关关联数据的基础知识。在讨论并创建不同数据之间的关系之前，让我们回顾一下我们为什么要这样做。这一切都归结于标准化。

In this chapter, we will understand the basics of relating data. Before talking about and creating relationships between different data, let us review why we need it. It all goes back to normalization.

Normalization

数据库标准化，或简称标准化，是组织关系数据库的列（属性）和表（关系）以最大程度地减少数据冗余的过程。这是将数据拆分为多个表以提高整体性能、完整性和生命周期的过程。

Database normalization, or simply normalization, is the process of organizing columns (attributes) and tables (relations) of a relational database to minimize data redundancy. It is the process of splitting data across multiple tables to improve overall performance, integrity and longevity.

  1. Normalization is the process of organizing data in a database.

  2. This includes creating tables and establishing relationships between those tables according to rules designed both to protect the data and to make the database more flexible by eliminating redundancy and inconsistent dependency.

现在让我们看看下表，其中包含数据，但问题是这些数据相当冗余，这增加了在数据输入期间出现错别字和措辞不一致的可能性。

Let us now look into the following table which contains data, but the problem is that this data is quite redundant which increases the chances of typo and inconsistent phrasing during data entry.

CustID

Name

Address

Cookie

Quantity

Price

Total

1

Ethel Smith

12 Main St, Arlington, VA 22201 S

Chocolate Chip

5

$2.00

$10.00

2

Tom Wilber

1234 Oak Dr., Pekin, IL 61555

Choc Chip

3

$2.00

$6.00

3

Ethil Smithy

12 Main St., Arlington, VA 22201

Chocolate Chip

5

$2.00

$10.00

为了解决此问题，我们需要重新构建我们的数据并将其分解为多个表以消除一些冗余，如下面的三张表所示。

To solve this problem, we need to restructure our data and break it down into multiple tables to eliminate some of those redundancy as shown in the following three tables.

three tables

在这里，我们有一张用于客户的表，第二张用于订单，第三张用于曲奇。

Here, we have one table for Customers, the 2nd one is for Orders and the 3rd one is for Cookies.

这里的问题是，仅将数据拆分为多个表并不能说明一张表中的数据如何与另一张表中的数据相关。要连接多张表中的数据，我们必须向 Orders 表中添加外键。

The problem here is that just by splitting the data in multiple tables will not help to tell how data from one table relates to data in another table. To connect data in multiple tables, we have to add foreign keys to the Orders table.

Defining Relationships

关系通过匹配关键列中的数据来工作，通常是两张表中具有相同名称的列。在大多数情况下，该关系将一张表的主键（为每行提供唯一标识符）与另一张表中外键中的条目匹配。表之间有三种类型的关系。创建的关系类型取决于如何定义相关列。

A relationship works by matching data in key columns usually columns with the same name in both the tables. In most cases, the relationship matches the primary key from one table, which provides a unique identifier for each row, with an entry in the foreign key in the other table. There are three types of relationships between tables. The type of relationship that is created depends on how the related columns are defined.

现在，让我们深入了解三种关系类型−

Let us now look into the three types of relationships −

One-to-Many Relationships

一对多的关系是最常见的关系类型。在这种关系类型中，表 A 中的一行可以在表 B 中有许多匹配的行，但表 B 中的一行只能在表 A 中有一行匹配的行。

A one-to-many relationship is the most common type of relationship. In this type of relationship, a row in table A can have many matching rows in table B, but a row in table B can have only one matching row in table A.

例如，“客户”表和“订单”表具有一个一对多的关系：每个客户可以下许多订单，但每个订单只能来自一个客户。

For example, the Customers and Orders tables have a one-to-many relationship: each customer can place many orders, but each order comes from only one customer.

Many-to-Many Relationships

在多对多关系中，表 A 中的一行可以有许多在表 B 中匹配的行，反之亦然。

In a many-to-many relationship, a row in table A can have many matching rows in table B, and vice versa.

您可以通过定义一个称为 junction table 的第三个表来创建这样的关系，其主键由表 A 和表 B 的外键组成。

You create such a relationship by defining a third table, called a junction table, whose primary key consists of the foreign keys from both table A and table B.

例如，“客户”表和“Cookie”表具有多对多关系，该关系由这两个表中与“订单”表的每个一对多关系定义。

For example, the Customers table and the Cookies table have a many-to-many relationship that is defined by a one-to-many relationship from each of these tables to the Orders table.

One-to-One Relationships

在一对一的关系中，表 A 中的一行最多只能在表 B 中有一行匹配的行，反之亦然。如果两个相关列都是主键或具有唯一约束，则会创建一对一的关系。

In a one-to-one relationship, a row in table A can have no more than one matching row in table B, and vice versa. A one-to-one relationship is created if both the related columns are primary keys or have unique constraints.

这种关系类型不常见，因为这样相关的大多数信息都将存储在一张表中。您可以将单一对一关系用于以下目的：

This type of relationship is not common because most information related in this way would be all in one table. You might use a one-to-one relationship to −

  1. Divide a table into many columns.

  2. Isolate part of a table for security reasons.

  3. Store data that is short-lived and could be easily deleted by simply deleting the table.

  4. Store information that applies only to a subset of the main table.