最近在做数据库设计，拿到一本ms press 的 《Designing Relational Database Systems》E文，看了几遍关系数据库设计过程一节，还是没记得住，决定仔细译一下。版权问题，扼要的译。
Defining RelationshipsAfter you've been through all your source documents, you'll have a draft description of the entities and attributes in the problem space. Two tasks remain: establishing the relationships between these entities, and reviewing each entity's attributes and constraints.
在当你过了一遍所有你的源文档后，你就会对问题域里实体及其属性有一个大概的了解。还有两项工作：在实体间建立他们之间的关系和检查实体的每一个属性和属性的约束。
Although theoretically you could review the attributes first, I find it easiest to start with the relationships, since some of these will become additional entities and some will require that attributes be added to entities you've already identified.
虽然理论上你应该先复查实体的属性先，但我发现从它他的关系入手将简单的多，它们之中的一些将被提取出来成新的实体而有一些则需要添加新的属性。
If you're like me, what you have after your first pass through the source documents is a bunch of handwritten notes with arrows and scribbles and "see page 12"s that nobody else could possibly decipher. So the first step in defining relationships is to get these organized neatly. You can start by building the first draft of the Entity Relationship (E/R) diagram of your data model. (If your notes are really messy and you're worried that even you might not be able to read them in three weeks, you might also list the attributes you've identified for each entity.)
如果你和我一样，你将在过了一遍所有的源文档后便用一些符号和手写注释起草，写一些诸如“请看第12页”这些无人知晓的东东。所以定义“关系”的第一步应该先把所有的东东齐整的组织一下。你可以根据你的数据模型的ER图来开始建立先张草图。（如果你的注释着实零乱，而且你害怕三个星期后你可能会忘记，你也可以把每一实体的属性列出来。）
Start by choosing an entity, usually one of the core entities in the system, and then add the entities that have some relationship with it. You can define the nature of the relationships (one-to-one, one-to-many, many-to-many) as you go, or you can simply draw a straight line to remind yourself that there is a relationship, and then come back and analyze it later. 从选择单一的实体开始，通常是系统的核心实体。然后添加一些与核心实体相关的实体。接着定义“关系”的种类（就是“关系”的内在属性）（一对一、一对多、多对多），或者你只是简单的画一条直线来提醒自己这里有一个“关系”，待后回来再加分析。
The first draft of the E/R diagram for the order-processing example is shown in Figure 9-4. This is a simple example, and the diagram is quite easy to read. (Assume we've decided that Salesperson is an attribute only of the Sales Order entity and not an entity in its own right.) If you're working on a complex example, you might want to create multiple diagrams, each one describing only a subset of the data. In this case, it's a good idea to use some sort of automated support for the diagrams. Otherwise, making sure they're synchronized can get tedious.
下面的是“订单过程”的实体-关系图的首张草图。这是一个简单的例子，图也很好理解（假设我们只把销售人作为销售订单的一个属性，而不是作为一独立的实体）。如果你在做一个比较复杂的例子，你也许会创建多的图，每一个图展示数据的子集。在这种情况下，你最好使用一些图的自动化支持，不然，使它们同步将乏味异常。
Once you have your draft E/R diagram prepared, you can begin to analyze the relationships in more detail. For each relationship, you want to determine the following:
当你准备好你的实体-关系草图后便可以更详细的分析这些“关系”了。对于每一个“关系”，你须确定如下几项：
The Cardinality of the Relationship
The Cardinality of the RelationshipThe Cardinality of the RelationshipThe Cardinality of the RelationshipWhere you discover a many-to-many relationship, you should add the junction entity to the model, with a one-to-many relationship on each side. The relationship between Supplier and Product in our model is many-to-many, and so we need to add the entity Product Supplier to resolve it. Note that the relationship between Sales Order and Product is also many-to-many, but in this case the Order Detail entity functions as the junction.
当你发现模型里存在“多对多”的关系，你必须添加一个新的实体作连接作用，使每一端只存在“一对多”的关系。在我们的模型中，供应商和商品之间是多对多的关系，所以我们添加了一个“商品供应商”实体分解它们。
The Optionality of the Relationship
The Optionality of the RelationshipThe Optionality of the RelationshipThe Optionality of the RelationshipHaving established the kind of the relationship between any two entities, you should now consider whether the relationship is optional for either or both participants. In our example, the relationship between Customer and Shipping Method is optional in both directions—that is, customers are not required to have a default shipping method, and shipping methods can exist without being used by a customer.
当完成建立任何两实体间的关系后，你还必须确定这些关系对于参与实体的一方或双方的可选性。在我们的例子里面，客户实体和运送方式实体间的关系在两个方向都是可选的—就是说，客户并不是必须拥有一个缺省的商品运送方式，而运送方式在还没有被客户使用时依然可以独立的存在。
The relationship between Product Category and Product, on the other hand, is only optional in one direction. A Product Category need not have products assigned to it, but all products must be assigned to a Product Category.
然而，在商品类别和商品之间的关系中，只是有一方是可选的。一种商品类别不必有一样商品归属于它中（可选的），但所有的商品都必须分门别类。
The relationship between Sales Order and Shipping Method is even more complex. A shipping method can exist independently of a sales order, so the Sales Order side of the relationship is optional. The Shipping Method side of the relationship, however, is optional only if the sales order has special instructions. This is an important constraint and should be noted on the diagram.
销售订单和运送方式之间的关系还要复杂一点。一种运送方式可以不依赖于销售订单而独立存在，所销售订单一端的关系是可选的。但在运送方式一端则只在销售订单含有特殊说明时才是可选的。这是这关系的一项重要的约束，必须在图上作出注解。 The Attributes of the Relationship
In most situations, all that you need to record about the relationship between two entities is the fact of its existence. We need to know, for example, that a specific Customer placed a given Sales Order, and that's all we need to know. Sometimes, however, we need to know additional facts about the relationship—when it began or how long it lasted, for example. These facts are attributes of the relationship itself, not of either of the participants.
在大多数的情况下，所有你要记录有关于实体间的关系的只是它的存在性。举个例子，我们需要知道某个客户下了一个销售订单，这就完了。但有时候，我们还需要知道一些“关系”额外的信息，比如关系什么时候开始，持续多长时间。这些都是关系本身的性质，而不是参与实体的性质。
Where the relationship itself has attributes, it must be modeled as an entity. In the order-processing example, we might decide to designate one Supplier as having "Preferred Supplier" status. Since we already have a junction entity between Product and Supplier, the Preferred Supplier attribute can simply be added to that entity. If that were not the case, we would have needed to add an entity to represent the attributes of the relationship.
当一个“关系”本身含有属性，那么它必须被提取出来作为一个新的实体。在我们的订单过程例子里，我们可能会指定某一供应商为首选的。因为之前我们已经有一个连接实体在商品和供应商之间了，所以“首选供应商”的属性可以简单地添加到那个实体就行了。如果没有这个中间的话，我们必须创建这个实体来展现“关系”的这种属性。
Additional Constraints on the Relationship
Additional Constraints on the RelationshipAdditional Constraints on the RelationshipAdditional Constraints on the RelationshipFinally, we want to consider whether any additional constraints pertain to the relationship. What are the minimum and maximum number of records that can exist on the many side of a one-to-many relationship? Are there any conditions that must be met before the relationship is allowed to exist? Are there any conditions under which the relationship must exist?
最后，我们还要考虑工“关系”是否还有其它额外的约束条件。比如说，在“一对多”关系的多的一端，实体实例最少和最多是多少，有没有限制；是否在额外的条件下“关系”才允许存在？有没有一些关系必须存在的前提条件？
In our example, the requirement that the relationship between a Sales Order and a Shipping Method is optional only if Special Instructions have been indicated is one such constraint. The rule that customers cannot place an order until after their credit has been approved is another. Again, this rule has been indicated by an annotation on the diagram. If there are many constraints or the constraints are too complex for a simple annotation, you might need to document them elsewhere. You should, however, at least indicate on the diagram that such constraints exist.
在我们的例子里边，销售订单和运送方式之间的关系是可选的，有一条约束，就当有特定的说明，它们之间存在关系。另外还有，客户在他的身份得到审核之前是不能下达订单的。这些关系的约束规则已经在图上作了注解。如果关系存在很多约束，而且这些约束很复杂以致于不能简单的在图上作注解，你必须选择在别处另作说明。但是你还是要在图上表明有这样的约束存在。
本文地址：http://com.8s8s.com/it/it19063.htm