第八周翻译作业

原文链接http://www.sqlservercentral.com/articles/Stairway+Series/69927/

作者：Joe Celko,

日期：2013/09/18 (first published: 2010/05/25)

此篇章为翻译外国作家著作，上文已标注好原文链接和作者，如有雷同，敬请谅解

 

Stairway to Database Design Level 3: Building Tables

数据库设计第3级的阶梯:构建表

By Joe Celko, 2013/09/18 (first published: 2010/05/25)

The Series

This article is part of the Stairway Series: Stairway to Database Design

这篇文章是阶梯系列（数据库设计阶梯）的一部分

New to the task of designing and creating a database? Joe Celko, who is one of the most widely read of all writers about SQL, explains the basics. As usual, he comes up with the occasional surprise for even the most seasoned database professional. Joe was the winner of the DBMS Magazine Reader's Choice Award four consecutive years. He has taught SQL in the US, UK, the Nordic countries, South America and Africa. He served 10 years on ANSI/ISO SQL Standards Committee and contributed to the SQL-89 and SQL-92 Standards.

重新设计任务并建库？Joe Celko是所有读者中对SQL涉猎广泛的，他解释数据库基础。通常来说，他会想出让老练的数据库师惊喜的点子。Joe是DBMS读者杂志连续好几年的获奖者，他曾在美国，北欧，英国，南美和非洲任教SQL。他也在ANSI /ISO SQL标准委员会工作了十年，并为SQL-89 和 SQL-92标准作出贡献

There are several types of tables, each with their special requirements for rules and integrity constraints. Whatever the requirement, table-level constraints will ensure that the rules are enforced and data integrity is maintained.

有几种类型的表，每个表都有对规则和完整性约束的特殊要求。无论需求如何，表级约束将确保规则被强制执行，数据完整性得到维护。

In level one, we named data elements for what they are and classified them. In level two, we modeled the data elements with data types and constraints in SQL to give us rows. In level three, we are going to put these rows into tables. A table is more than just a bunch of rows collected together under one name.

在第一阶梯，我们把数据元素叫做元素并分类。在第二阶梯，我们根据数据类型和数据约束把数据元素建模，以提供行。在第三阶梯，我们将这些行插入表格，表不仅仅是一组以一个名字组合在一起的行。

A column can appear only once in a table. That only makes sense; if you recorded someone's shoe size twice, it would be redundant at best and ambiguous when the columns disagree at worst. Now we can have table level CHECK constraints among the columns of each row. They are not really that much different than the one column CHECK constraints we had before. They can be named and will appear in the list of column declarations in the CREATE TABLE statement, not attached to any row. . For example:

一个列在表格中只能出现一次。这样才有意义，如果你记某个人鞋的尺码两次，最好的结果是它多余了，最坏的结果是它会重复，现在可以在每行的列中有表级检查约束。它们与我们之前的一列检查约束没有太大的不同。它们可以被命名，并将出现在CREATE TABLE语句的列声明列表中，他们可以被命名出现在一系列，而不附加任何行例如:

CONSTRAINT Valid_Employee_Age-- don't hire people before they are born

 CHECK (emp_birth_date < emp_hire_date)

 

It is often a good idea to not combine constraints into one huge CHECK () clause. The error messages will include the constraint name, so separate constraints will give you a better idea of what went wrong than a single monster named  “Bad_Things_Happened” constraint.

不将约束合并到一个巨大的CHECK()子句中通常是个好主意。错误消息将包含约束名称，因此单独的约束将使您更好地了解错误，而不是一个名为“bad_things_发生”约束的单个怪物。

Continuing our hatred of redundancy, at the table level we want each row to be unique for the same reasons. This can be done with table constraints. The two table level constraints are UNIQUE and PRIMARY KEY, which come in both single and multiple column versions.

继续谈谈我们厌恶的冗余，在表格关系中我们想让每一行都独一无二。这可以通过表格约束做到。那两个表格主键约束都是唯一的，它们同时出现在单个和多个列版本中。

The UNIQUE constraint says that the column or combination of columns is unique within the table. But if there is a NULL in one or more of the columns, we will allow it as if it were a unique value. The PRIMARY KEY declaration has the same effect as a NOT NULL and UNIQUE for all the columns in it. But for historical reasons, a table can have only one PRIMARY KEY declaration. These columns are used as defaults for other constraints between tables, but don't worry about that for now.

唯一的约束表示列或列的组合在表中是唯一的。但是如果有空值出现在一列或多列表格里，如果它有一个唯一的值我们是允许的。主键约束对列的非空和唯一也有同样的作用。出于历史原因，一个表格只能有一个主键，这些列都有默认值在表格约束下，但是现在可以不用担心这个

How uniqueness constraints are used depends on the type of table involved. Broadly speaking, we can classify a table as one of three kinds:

1. Entity
2. Relationship
3. Auxiliary

如何使用唯一性约束如何使用取决于表格类型。广泛来说，我们可以把表格分为三大类

实体

关系

辅助

 

An entity table is a set of things of the same kind which are defined by the attributes modeled by the columns. Each row is an instance of that kind of thing. Each row has the same columns. If you can see it feel, see or touch it, then it is an entity. The name of a entity table should not be singular (unless there really is one only member of this set) because it models a set. The name needs to be plural or, if possible, collective. For example, “Employee” is bad, “Employees” is better and “Personnel” is best. “Tree” is bad, “Trees” is better and “Forest” is best. You can add your own examples.

一个实体表格是被属性定义被列建模相同种类的事物。每行都是相同种类的事物。每行都有相同的组合，如果你可以柑橘看见或触摸他，他就是一个实体。一个实体表格的名称不应该是单数（除非这里只有一个被设置的成员）。名字需要复杂一点，如果可能的话是一个复数，数组。例如，应聘者是不好的，应聘者们更好，个人是好的，树是不好的很多书好一点，森林做好，你可以家长自己的例子

Entities are also classified as weak or strong. A strong entity exists on its own merit, while a weak entity exists because of one or more strong entities. You need to have a purchase before you can have a discount.

实体也分强弱，一个强的实体有自己的主键，而一个弱的实体存在一个或多个抢的实体。你需要买才能有折扣。

A relationship table references one or more entities and establishes a relationship among them. A relationship can have attributes of its own in addition to references to entities. The marriage license number belongs to the marriage, not the husband, wife or minister.

一个关系表格指出一个或多个实体建立他们之间的关系。一个关系除了引用的实体以外可以有他们自己的属性，就像婚姻关系属于婚姻，不属于丈夫妻子或者牧师

The degree of a relationship is the number of entities in the relationship. Binary relationships have two entities and we like them in the real world because they are simple. A recursive binary relationship relates an entity to itself. The general n-ary relationship involves n entities, such as a home mortgage with a buyer, seller and lender. It is not always possible to decompose n-ary relationships into binary relationships. Membership in the relationship can be optional or mandatory. The optional membership means we can have zero entities of one kind – a purchase does not always get a discount.

关系的程度是关系实体的数量。二元关系只有两个实体，我们喜欢他们出现在现实世界中因为他们很简单。一个循环二元关系和实体相联系。一般的N-1的关系包括N个实体，就像一个房屋销售和一个买者，卖者和买者。把N元关系转换成二元关系也不是不可能的。关系中的成员资格可以是可选的，也可以是强制性的。可选会员意味着我们可以拥有一种零实体——一种购买并不总能得到折扣。

The cardinality of a relationship is the actual number of related occurrences for each of the two entities. The basic types of connectivity for relations are: one-to-one, one-to-many, and many-to-many. These terms are usually qualified with optional (0 or more) or mandatory (1 or more) memberships.

关系的基数是两个实体相互联系的真实数量。这些关系联系基本种类是，一对一，一对多，多对多。这些物体通常是可以选择或强制的关系

A one-to-one (1:1) relationship is when at most one instance of an entity A is associated with one instance of entity B. For example, take the relationship between a traditional husband and wife. Each husband has one and only one wife; each wife has one and only one husband. Both are mandatory in this example.

一个一对一关系是当一个物体对应至多一个实体。A是关联于B的，例如，拿丈夫和妻子来说，每个丈夫只能有一个妻子，每个妻子也只能有一个丈夫，在这个例子中，两者都是强制性的。

A one-to-many (1:n) relationship is when for one instance of entity A, there are zero, one or many instances of entity B but for one instance of entity B, there is only one instance of entity A. An example might be that a department has many employees; each employee is assigned to one department. Depending on your business rules, you might allow an unassigned employee or an empty department.

一对多的关系是一个实体A对应（无，一个或者多个）实体B，但是A只能有一个。一个例子可能是一个部门有许多员工;每个员工被分配到一个部门。根据您的业务规则，您可能允许未分配的员工或空部门。

many-to-many (m:n) relationship, sometimes called non-specific, is when for one instance of entity A, there are zero, one, or many instances of entity B and for one instance of entity B there are zero, one, or many instances of entity A. An example might be pizzas and customers.

多对多的联系，有些时候叫普通，对于一个实体A 都会有（0,1，或者多个）实体B 例如一个实体B也对应（0,1或者多个）实体A。匹萨和顾客也是一个很好的例子。

An auxiliary table is neither an entity nor a relationship; it provided information. They are things like calendars or other look up tables that replace computations in SQL. They are often misunderstood and get treated like entity or relationship tables.

一个辅助表格不是实体也不是关系，它提供信息，它们类似于日历或其他查找表，以SQL代替计算，他们经常被误解会被当成实体或者关系

Let's make this more concrete. A sales order is a relationship between a customer (entity) and our inventory (entity). The order details are a weak entity that exists because we have an order. The relationship has an order number that is not part of the inventory or customer. The shipping costs are obtained from an auxiliary table. Here are some skeleton tables for this example. I am using the GTIN (Global Trade Item Number) for order items and the DUNS (Data Universal Numbering System) for the customers. Always look for industry standards when you design a database.

具体化一点来说，一个顾客订单是顾客（实体）和我们的库存的关系。一个订单细节是一个弱实体因为我们拥有一个订单，关系有一个订单号不是库存或者客户的一部分。运费由一个辅助表获得。下面是这个例子的一些框架表。我正在使用GTIN(全球贸易项目编号)为客户订购项目和DUNS(数据通用编号系统)。在设计数据库时，一定要注意行业标准。

CREATE TABLE Sales_Orders

(order_nbr INTEGER NOT NULL PRIMARY KEY

 CHECK (order_nbr > 0),

 customer_duns CHAR(9) NOT NULL,

 order_shipping_amt DECIMAL (5,2) NOT NULL

 CHECK (shipping_amt >= 0.00),

 etc);

CREATE TABLE Sales_Order_Details

(order_nbr INTEGER NOT NULL,

 gtin CHAR(15) NOT NULL,

 PRIMARY KEY (order_nbr, gtin),

 item_qty INTEGER NOT NULL

 CHECK (item_qty > 0),

 item_unit_price DECIMAL (8,2) NOT NULL

 CHECK (item_unit_price >=0.00));

CREATE TABLE Customers

(customer_duns CHAR(9) NOT NULL PRIMARY KEY

 CHECK (customer_duns LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),

 etc);

CREATE TABLE Inventory

(gtin CHAR(15) NOT NULL PRIMARY KEY

 CHECK (gtin LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),

 onhand_qty INTEGER NOT NULL

 CHECK (onhand_qty >= 0),

We can see that the Sales Order is a relation between the Customers and Inventory. Orders have their own key (order_nbr) but there is nothing to force us to use only valid customer DUNS numbers or product GTIN codes for things we actually have in inventory. In fact, I can insert obviously invalid DUNS and GTIN codes into the Orders table the way things are declared now.

我们可以看到售货订单是一个顾客和库存的联系。订单有他们自己的键，但是没有什么可以强迫我们只使用有效的客户DUNS号或产品GTIN代码来做我们实际有库存的东西。事实上，我可以将明显无效的DUNS和GTIN代码插入到Orders表中。

This is where the REFERENCES clause comes in. It is what lets us enforce all that cardinality and degree stuff from the data model. A reference is not a link or a pointer. Those were physical concepts and reference is a logical concept and we have no idea how it is implemented. What it enforces is a rule that the referencing table columns match a single row in the referenced table. That means the row in the referenced table has to be unique; by default, the PRIMARY KEY in the referenced table is the target, but it dos not have to be.  The values in the referencing table are called Foreign Keys – they are not keys in their table, but somewhere else in the schema.

Here is the skeleton schema with more flesh on it:

这就是引用子句的由来。它让我们能够强制执行所有基数和度数据。一个联系不是连接或者指向。这些是物理概念，联系是逻辑概念，我们不知道它应该如何应用，它强制的是指向表格列和行匹配必须唯一的规则，这意味着行在关系表格必须是唯一的。默认。主键在引用表格是目标，但是也可以不必这样，这些在关系表格的值成为外键，他们在另一张表格里不是主键，在其他表格是

这是更新的骨架表格

CREATE TABLE Sales_Orders

(order_nbr INTEGER NOT NULL PRIMARY KEY

 CHECK (order_nbr > 0),

 customer_duns CHAR(9) NOT NULL

 REFERENCES Customers(customer_duns),

 order_shipping_amt DECIMAL (5,2) DEFAULT 0.00 NOT NULL

 CHECK (shipping_amt >= 0.00),

 etc);

CREATE TABLE Sales_Order_Details

(order_nbr INTEGER NOT NULL

 REFERENCES Orders(order_nbr),

 gtin CHAR(15) NOT NULL

 REFERENCES Inventory(gtin),

 PRIMARY KEY (order_nbr, gtin),-- two column key

 item_qty INTEGER NOT NULL

 CHECK (item_qty > 0),

 item_unit_price DECIMAL (8,2) NOT NULL

 CHECK (item_unit_price >= 0.00));

CREATE TABLE Customers

(customer_duns CHAR(9) NOT NULL PRIMARY KEY

 CHECK (customer_duns LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),

 etc);

CREATE TABLE Inventory

(gtin CHAR(15) NOT NULL PRIMARY KEY

 CHECK (gtin LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),

 onhand_qty INTEGER NOT NULL

 CHECK (onhand_qty >= 0),

 etc);

Notice that we only had to have CHECK () constraints on the places where DUNS and GTIN were keys, not where they appear in the referencing tables. The entity tables, Customers and Inventory, are referenced; the relationship table, Orders, references other tables. This is a general pattern, but it is not set in concrete.

注意，我们只需要在DUNS和GTIN是键的地方设置CHECK()约束，而不是在引用表中出现的地方。引用实体表、客户和库存;关系表、订单、引用其他表。这是一个一般的模式，但不是具体的

The multiple column form of this clauses looks like this:

此子句的多个列形式如下:

FOREIGN KEY (order_nbr, gtin)

REFERENCES Sales_Order_Details(order_nbr, gtin)

The columns in the FOREIGN KEY sub-clause are in the referencing table have to match the referenced key, column for column, but could have different names. I can get the 1:1, 1:n and n:m relationships by placing uniqueness constraints in the right places.  As an example of an axillary table, we might compute the shipping costs based on the total value of the order. The table could look like this: 

外键子句中的列在引用表中，必须匹配列的引用键，但可以有不同的名称。我可以通过在正确的位置放置唯一性约束来得到1:1、1、n和n:m的关系。作为一个腋窝表的例子，我们可以根据订单的总价值计算运费。表格可以是这样的

CREATE TABLE Shipping_Costs

(start_order_amt_tot DECIMAL (10,2) NOT NULL,

 end_order_amt_tot DECIMAL (10,2) NOT NULL,

CONSTRAINT Valid_Shipping_Range

 CHECK (start_order_amt_tot < end_order_amt_tot),

PRIMARY KEY (start_order_amt_tot, end_order_amt_tot),

 shipping_amt DECIMAL (5,2) NOT NULL

 CHECK (shipping_amt > 0.00));

 

While we have declared a PRIMARY KEY on the auxiliary shipping costs table, it is not like the keys for entities – there is no validation or verification, it is not an identifier. To use this table, we will query with something like:

当我们在腋窝表中表明一个主键，它不像键对应实体，没有验证或者认证，这是无效的，不是定义，为了用这个表格，我们会像这样查询：

SELECT shipping_amt

  FROM Shipping_Costs

 WHERE <order amount total> BETWEEN start_order_amt_tot AND end_order_amt_tot;

As an exercise, try to write a  constraint that will prevent the start and end ranges from overlapping and from having gaps.  You can re-design the table if you need to.

作为联系，试着去屑一个约束，会防止超出开始和结束范围，你可以重新设计表格如果你需要的话

In the revised skeleton schema, when you try to take an order for a product that is not in inventory, you will get an error that says, in effect, “it is out of stock!” and you can try something else. But if you try to delete a product from Inventory, you will also get an error says in effect, “Hey, someone ordered this junk”, so you have to go to each order and replace the item with something else or make it NULL (if allowed) before you can delete it from Inventory.

在骨架表格里，当你试着去为一个产品下载订单时，你会得到一个错误表示，这个超出库存范围了，然后你可以试着尝试其他，但是如果你试着从库存中删除一个物品你会同样得到一个错误提示

This is where Declarative Referential Integrity (DRI) actions are used. The syntax is:

这是使用声明引用完整性(DRI)操作的地方。语法是:

ON DELETE [NO ACTION | SET DEFAULT | SET NULL | CASCADE]

ON UPDATE [NO ACTION | SET DEFAULT | SET NULL | CASCADE]

The delete and update are called “data base events”; when they happen to the table, then the DRI action occurs.

删除和更新都叫做数据大事件，当他们在表格中发生时，DRI发生

1. NO ACTION = the transaction is rolled back and you get a message. This is the default when you just have a simple REFERENCES clause.

不发生动作事件，回滚事件，当你得到一条简单信息时，你会得到一条简单信息当你只有简单的信息

1. SET DEFAULT = the referenced column(s) are changed by the event, but the referencing columns are changed to their default values. Obviously, the referencing columns need to have defaults declared on them. Those defaults have to be in the referenced table.

SET DEFAULT =引用的列由事件改变，但是引用列被更改为默认值。显然，引用列需要在它们上声明默认值。这些默认值必须在引用表中。

1. SET NULL  = the referenced column(s) are changed by the event, but the referencing columns are changed to NULLs. Obviously, the referencing columns need to be NULL-able. This is where the “benefit of the doubt” for NULLs comes in.

 .项目SET NULL =引用的列被事件更改，但是引用列被更改为NULLs。显然，引用列需要为空。这就是“怀疑的好处”的原因。

CASCADE =  the referenced column(s) are changed by the event, and those same values are cascaded over to  the referencing columns. This is the most important option in practice. For example, if we want to discontinue a product, we can delete it from Inventory and the ON DELETE CASCADE will make the SQL engine automatically delete the matching rows in the Sales_Order_Details. Likewise, if you update an item in Inventory , ON UPDATE CASCADE will autocratically replace the old value with the new wherever it is referenced.

级联=被引用的列被事件改变，相同的值被级联到引用列。这是实践中最重要的选择。例如，如果我们想要停止一个产品，我们可以从库存中删除它，而在delete CASCADE中，SQL引擎会自动删除Sales_Order_Details中的匹配行。同样，如果您在库存中更新项目，则更新级联将在引用的任何地方将旧值替换为新值。

After any of these actions are performed, the referential integrity constraints are still valid. Here is the final skeleton:

在这些行为和表示之后，那个引用实体约束仍然是有用的，这是骨架

CREATE TABLE Sales_Orders

(order_nbr INTEGER NOT NULL PRIMARY KEY

 CHECK (order_nbr > 0),

 customer_duns CHAR(9) NOT NULL

 REFERENCES Customers(customer_duns)

 ON UPDATE CASCADE

 ON DELETE CASCADE,

 order_shipping_amt DECIMAL (5,2) DEFAULT 0.00 NOT NULL

 CHECK (shipping_amt >= 0.00),

 etc);

CREATE TABLE Sales_Order_Details

(order_nbr INTEGER NOT NULL

 REFERENCES Orders(order_nbr)

 ON UPDATE CASCADE

 ON DELETE CASCADE,

 gtin CHAR(15) NOT NULL

 REFERENCES Inventory(gtin)

 ON UPDATE CASCADE

 ON DELETE CASCADE,

 PRIMARY KEY (order_nbr, gtin),-- two column key

 item_qty INTEGER NOT NULL

 CHECK (item_qty > 0),

 item_unit_price DECIMAL (8,2) NOT NULL

 CHECK (item_unit_price >= 0.00));

See if you can figure out what happens when:

1. A customer dies and we delete him.
2. We change the Lawn Gnome statue to the more tasteful Pink Flamingo.
3. We discontinue the Pink Flamingo.
4. Someone tried to order a Lawn Gnome after steps 1 to 3

Obviously, I am leaving out restocking issues and other things, but we will get to those.

The Series

Read about the rest of The Stairway to Database Design Series and view the other articles.

 看看你是否能弄清楚发生了什么事:

1 .工作一个客户去世了，我们把他删除了。

2.我们把草坪上的侏儒雕像换成了更有品位的火烈鸟。

3 .项目我们停止了粉红色的火烈鸟。

4。在第1到3步之后，有人试图订购草坪Gnome

很明显，我不考虑重新进货的问题和其他事情，但我们将会得到这些。

该系列

阅读其他的楼梯到数据库设计系列和查看其他文章。

This article is part of the Stairway to Database Design Stairway

这篇文章是通往数据库设计楼梯的楼梯的一部分