从类模型映射到关系模型

根据领域模型分析数据模型

Mapping from the Class Model to the Relational Model 从类模型映射到关系模型

Having described the two domains of interest and the notation to be used, we can now turn our attention as to how to map or translate from one domain to the other. The strategy and sequence presented below is meant to be suggestive rather than proscriptive - adapt the steps and procedures to your personal requirements and environment.

在需要对两个领域建模时，现在我们可以关注如何从一个领域映射或转换映射到另一个领域。以下的策略和方法，就是要启发，而不是强制的步骤和程序应用到您的个人需求和环境。

1. Model Classes

Firstly we will assume we are engineering a new relational database schema from a class model we have created. This is obviously the easiest direction as the models remain under our control and we can optimise the relational data model to the class model. In the real world it may be that you need to layer a class model on top of a legacy data model - a more difficult situation and one that presents its own challenges. For the current discussion will focus on the first situation. At a minimum, your class model should capture associations, inheritance and aggregation between elements.

1. 类建模

首先，我们将假设从已创建的类模型生成一个新的关系数据库模型。这显然是最容易的，可控制的，可以通过关系数据库模型反向优化类模型。在现实世界中可能你需要将类模型作为数据模型的上层，这是更困难的情况，也对自己提出了一个挑战。对于目前的讨论将集中在第一种情况。至少，你的类模型应捕元素之间的联系、继承和聚集关系。

2. Identify persistent objects

Having built our class model we need to separate it into those elements that require persistence and those that do not. For example, if we have designed our application using the Model-View-Controller design pattern, then only classes in the model section would require persistent state.

2. 确认持久对象

已建成的类模型，我们需要区分这些元素，那些需要持久化和那些不是。例如，如果我们设计我们的应用程序使用Model - View- Controller设计模式，那么只有MODEL模型部分需要持久化状态。

3. Assume each persistent class maps to one relational table

A fairly big assumption, but one that works in most cases (leaving the inheritance issue aside for the moment). In the simplest model a class from the logical model maps to a relational table, either in whole or in part. The logical extension of this is that a single object (or instance of a class) maps to a single table row.

3. 假设一个持久化类映射一个关系表

大多数情况下，这都是一个合理的假设，除去继承问题以外（暂且不考虑）。在最简单的模型中，逻辑模型中的一个类映射一个关系表的全部或一部分。这种逻辑的延伸是一个单一的对象（或类的实例）映射关系表中的一行数据。

4. Select an inheritance strategy.

Inheritance is perhaps the most problematic relationship and logical construct from the object-oriented model that requires translating into the relational model. The relational space is essentially flat, every entity being complete in its self, while the object model is often quite deep with a well-developed class hierarchy. The deep class model may have many layers of inherited attributes and behaviour, resulting in a final, fully featured object at run-time. There are three basic ways to handle the translation of inheritance to a relational model:

4. 选择一个继承策略

从面向对象模型转化成关系模型的过程中，继承可能是最有问题的关系和逻辑结构。关系空间本质上是平面结构，每一个实体自我实现，而对象模型通常为多层次扩展的类结构。多层级类模型可能有多层次继承的属性和行为，最终结果，运行时功能齐全的对象。有三种基本方式对继承关系转换成关系模型：

a.Each class hierarchy has a single corresponding table that contains all the inherited attributes for all

elements - this table is therefore the union of every class in the hierarchy. For example, Person, Parent, Child and Grandchild may all form a single class hierarchy, and elements from each will appear in the same relational table;

类层次结构有只有一个相应的表，包含所有元素的所有继承的属性。因此这个表是类层次结构中所有类的联合。例如，人，父母，子女和孙子都可能形成一个单一的类层次结构，单所有元素将出现在同一个关系表中;

b.Each class in the hierarchy has a corresponding table of only the attributes accessible by that class

(including inherited attributes). For example, if Child is inherited from Person only, then the table will contain elements of Person and Child only;

层次结构中每个类都有一个相应的表。表中包含类所要访问的所有属性（包括继承的属性）。例如，如果孩子继承人，则该表将包含个人与儿童所有的元素;

c.Each generation in the class hierarchy has a table containing only that generation's actual attributes. For

example, Child will map to a single table with Child attributes only

在类结构中，每一层有一个表，包含这一层的实际属性。例如，Child将映射大偶一个简单表，只包含汉字的属性。

There are cases to be made for each approach, but I would suggest the simplest, easiest to maintain and less error prone is the third option. The first option provides the best performance at run-time and the second is a compromise between the first and last. The first option flattens the hierarchy and locates all attributes in one table - convenient for updates and retrievals of any class in the hierarchy, but difficult to authenticate and maintain. Business rules associated with a row are hard to implement, as each row may be instantiated as any object in the hierarchy. The dependencies between columns can become quite complicated. In addition, an update to any class in the hierarchy will potentially impact every other class in the hierarchy, as columns are added, deleted or modified from the table.

以上的方法都有成功的案例，但我认为最简单，最容易维护和减少出错是第三个选择。第一个选项提供在运行时的最佳性能，第二个是第一个和最后一个方法的妥协。第一个选项把层次结构变成了平面结构，定义所有属性在一个表中-便于更新和检索层次结构中任何类，但难以验证和维护。与行相关的业务规则难以实现，因为每行可以作为层次结构中任何对象实例化。列间的依赖关系可能会变得非常复杂。此外，在更新层次结构中的任何类将有可能影响层次结构中的所有其

他类，例如对表进行添加，删除或修改列。

The second option is a compromise that provides better encapsulation and eliminates empty columns. However, a change to a parent class may need to be replicated in many child tables. Even worse, the parental data in two or more child classes may be redundantly stored in many tables; if a parent's attributes are modified, there is considerable effort in locating dependent children and updating the affected rows.

第二种选择是一个妥协方案，提供更好的封装和消除空列。但是，对父类的改变可能需要重复对多个子表的进行修改。更糟的是，在两个或更多孩子的父类数据可能冗余存储在多个表中，如果一个父类的属性被修改，那么定位产生影响的行数据的子表，并进行修改，将需要非常大的工作。

The third option more accurately reflects the object model, with each class in the hierarchy mapped to its own independent table. Updates to parents or children are localised in the correct space. Maintenance is also relatively easier, as any modification of an entity is restricted to a single relational table also. The down side is the need to re-construct the hierarchy at run-time to accurately re-create a child class's state. A Child object may require a Person member variable to represent their model parentage. As both require loading, two database calls are required to initialise one object. As the hierarchy deepens, with more generations, the number of database calls required to initialise or update a single object increases.

第三个方案更准确地反映了对象模型，在层次结构中每个类映射到它自己独立的表中。父类和子类的更新定位于正确的空间。维护也相对容易，任何一个实体的改变也能限制到一个单一的关系表中。不利的一面是，在运行时需要重新构造层次结构，以准确的重建子类的状态。子对象可能需要一个角色成员变量来表示他们的父子模型关系。初始化一个对象时需要两个表同时加载，两次数据库调用。对于多层结构，初始化或更新一个简单对象时，数据库调用的数量增加。

It is important to understand the issues that arise when you map inheritance onto a relational model, so you can decide which solution is right for you.

重要的是要了解把继承关系映射到关系模型可能出现的问题，以便决定哪个解决方案最适合您。

5. For each class add a unique object identifier

In both the relational and the object world, there is the need to uniquely identify an object or entity. In the object model, non-persistent objects at run-time are typically identified by direct reference or by a pointer to the object. Once an object is created, we can refer to it by its run-time identity. However, if we write out an object to storage, the problem is how to retrieve the exact same instance on demand. The most convenient method is to define an OID (object identifier) that is guaranteed to be unique in the namespace of interest. This may be at the class, package or system level, depending on actual requirements.

5. 为每个类添加唯一对象标识

运行时，非持久性对象通常由直接引用或者指针进行标识。一旦对象被创建，我们能够参考它通过它的运行时标识。但是，如果我们把对象输出到存储介质中，问题是当需要时，如何准确的重新获取相同的实例。最简便的方法是定义一个OID（对象标识符），是保证在关注的命名空间唯一性。也可能是在类，包或系统级中根据实际要求进行定义。

An example of a system level OID might be a GUID (globally unique identifier) created with Microsoft's 'guidgen' tool; eg. {A1A68E8E-CD92-420b-BDA7-118F847B71EB}. A class level OID might be implemented using a simple numeric (eg. 32 bit counter). If an object holds references to other objects, it may do so using their OID.

A complete run-time scenario can then be loaded from storage reasonably efficiently. An important point about the OID values above is that they have no inherent meaning beyond simple identity. They are only logical pointers and nothing more. In the relational model, the situation is often quite different.

一个系统级的OID可能是一个微软的'guidgen'工具创建的GUID（全局唯一标识符）;例如。 {A1A68E8E- CD92-420b- BDA7-118F847B71EB}。类级别的OID可能是通过使用一个简单的数字（如32位计数器）。如果一个对象包含其他对象的引用，它可以使用他们的OID。在运行时，一个完整结构可以从存储介质中被合理有效载入。一个关于上述OID值重要的一点是，他们没有超越作为简单标识以外的含义。他们是唯一合乎逻辑的指针而已。在关系模型中，情况往往大不相同。

Identity in the relational model is normally implemented with a primary key. A primary key is a set of columns in a table that together uniquely identify a row. For example, name and address may uniquely identify a

'Customer'. Where other entities, such as a 'Salesperson', reference the 'Customer', they implement a foreign key based on the 'Customer' primary key. The problem with this approach for our purposes is the impact of having business information (such as customer name and address) embedded in the identifier. Imagine three or four tables all have foreign keys based on the customer primary key, and a system change requires the customer primary key to change (for example to include 'customer type'). The work required to modify both the 'customer' table and the entities related by foreign key is quite large.

在关系模型中的标识通常作为一个主键。主键是一个表中唯一标识一行的列集合。例如，姓名和地址可以唯一地标识一个'客户'。当其他实体，例如，一个'推销员'，参考'客户'时，以'客户'的主键为基础，创建一个外键的。用这个方法的问题是有业务信息在标识中（如客户名称和地址），可能跟真实目的产生冲突。想象一下，三个或四个表中都有外键和基于客户表的主键，如果一个系统需要改变客户表的主键（例如包括'客户类型'）。修改'客户'表和通过外键关联的实体的工作是相当大的。

On the other hand, if an OID was implemented as the primary key and formed the foreign key for other tables, the scope of the change is limited to the primary table and the impact of the change is therefore much less. Also, in practice, a primary key based on business data may be subject to change. For example a customer may

change address or name. In this case the changes must be propagated correctly to all other related entities, not to mention the difficulty of changing information that is part of the primary key.

另一方面，如果OID作为主键，形成了对其他表的外键，更改范围仅限于主表，改变的影响要少得多。此外，在实践中，基于业务数据的主键可能会有所变动。例如，一个客户可能会变更地址或名称。在这种情况下，更改必须正确地传播到所有其他相关的实体，改变主键的一部分是更困难的。

An OID always refers to the same entity - no matter what other information changes. In the above example, a customer may change name or address and the related tables require no change. When mapping object models into relational tables, it is often more convenient to implement absolute identity using OID's rather than business related primary keys. The OID as primary and foreign key approach will usually give better load and update times for objects and minimise maintenance effort. In practice, a business related primary key might be replaced with:

● A uniqueness constraint or index on the columns concerned;

●Business rules embedded in the class behaviour;

● A combination of 1 and 2.

Again, the decision to use meaningful keys or OID's will depend on the exact requirements of the system being developed.

一个OID通常指的是同一个实体- 无论什么其他信息发生变化。在上述例子中，客户可能会改变名称或地址以及相关的表不需要改变。当映射对象模型到关系表中，用OID 作为绝对标识比使用业务相关主键实现往往更方便。OID作为主键和外键通常会更好的提供加载和更新对象的方法，减少维护工作量。在实践中，业务相关的主键可能被替换为：

●在相关列上建立唯一性约束和索引

●业务规则嵌入到类行为中;

●建立1和2的联系.

同样，决定使用有意义的钥匙或OID的依赖于对系统发展的真实需求。

6. Map attributes to columns

In general we will map the simple data attributes of a class to columns in the relational table. For example a text and number field may represent a person's name and age respectively. This sort of direct mapping should pose no problem - simply select the appropriate data type in the vendor's relational model to host your class attribute.

For complex attributes (ie. attributes that are other objects) use the approach detailed below for handling associations and aggregation.

6.属性映射到列字段中。

一般我们会把类中简单数据属性映射到关系表中的列字段中。例如，一个人的名字和年龄可以分别表示成文本和数字类型。这种直接映射应不成问题-只需在关系模型中选择适当的数据类型来承载类的属性。

对于复杂属性（即其他对象的属性集合）的使用方法在下面处理关联和聚合关系的部分进行详细说明。

7. Map associations to foreign keys

More complex class attributes (ie. those which represent other classes), are usually modelled as associations. An association is a structural relationship between objects. For example, a Person may live at an Address. While this could be modelled as a Person has City, Street and Zip attributes, in both the object and the relational world we are inclined to structure this information as a separate entity, an Address. In the object domain an address represents a unique physical object, possibly with a unique OID. In the relational, an address may be a row in an Address table, with other entities having a foreign key to the Address primary key.

In both models then, there is the tendency to move the address information into a separate entity. This helps to avoid redundant data and improves maintainability. So for each association in the class model, consider creating a foreign key from the child to the parent table.

7.关联映射到主键上

更复杂的类属性（即表示其他类集合），通常建模为关联关系。关联关系是对象之间的结构关系。例如，一个人可能在一个地址上居住。虽然这可以建模成一个人包含城市，街道和邮编属性，无论在对象和关系世界，我们倾向于地址信息结构作为一个单独的实体。在对象领域，一个地址表示一个唯一的的物理对象，可能拥有一个唯一的OID。在关系领域中，一个地址可能是一个地址表中的一行，其他实体通过外键关联到地址的主键上。

在这两种模型，有一个趋势，地址信息作为一个单独的实体。这有助于避免冗余数据，提高可维护性。因此，在类模型中的每个关联关系，将考虑创建从子表到主表的外键关系。

8. Map Aggregation and Composition

Aggregation and composition relationships are similar to the association relationship and map to tables related by primary-foreign key pairs. There are however, some points to bear in mind. Ordinary aggregation (the weak form) models relationships such as a Person resides at one or more Addresses. In this instance, more than one person could live at the same address, and if the Person ceased to exist, the Addresses associated with them would still exist. This example parallels the many-to-many relationship in relational terminology, and is usually implemented as a separate table containing a mapping of primary keys from one table to the primary keys of another.

8.关联映射到主键上

聚集和组成关系和关联关系相似，也是通过一对主外建关系映射到关系表中。然而，一些要点需要注意。普通聚合模型关系（弱形式），比如一个人居住在一个或多个地址。在这种情况下，不止一个人可以生活在同一个地址，如果人不再存在，与之相关的地址依然存在。这个例子中类似于关系模型中的多对多关系，通常是作为一个单独表来实现，包含从一个表的主键映射到另一个表的主键的关系。

A second example of the weak form of aggregation is where an entity has use or exclusive ownership of another. For example, a Person entity aggregates a set of shares. This implies a Person may be associated with zero or more shares from a Share table, but each Share may be associated with zero or one Person. If the Person ceases to exist, the Shares become un-owned or are passed to another Person. In the relational world, this could be implemented as each Share having an 'owner' column which stored a Person ID (or OID) .

第二个作为弱形式聚集的例子就是一个实体和它使用或者专属的事物。例如，一个人书体聚合了他的股票的集合。这意味着一个人可能拥有零或者更多来自股票表的股票，但每个股份可能属于零或者一人。如果这个人不再存在，其股份就成为没有拥有者或者分给另一个人。在这种关系世界，这可以被实现为每个股票有一个存储人的ID（或者OID）的‘拥有者’列。

The strong form of aggregation, however, has important integrity constraints associated with it. Composition, implies that an entity is composed of parts, and those parts have a dependent relationship to the whole. For example, a Person may have identifying documents such as a Passport, Birth Certificate, Driver's License & etc.

A Person entity may be composed of the set of such identifying documents. If the Person is deleted from the system, then the identifying documents must be deleted also, as they are mapped to a unique individual.

较强的聚合形式，和它相关的约束具有重要的完整性。组合关系，意味着一个实体整体由部分组成，这些部分有需要扶养的与整体的关系。例如，唯一标识一个人实体的证明可以是护照，出生证明，驾驶执照等，人的实体可以由以上的人的身份证明组成。如果此人从系统中被删除，则身份证明也必须同时删除，因为它们映射到一个唯一的个体。

If we ignore the OID issue for the moment, a weak aggregation could be implemented using either an intermediate table (for the many-to-many case) or with a foreign key in the aggregated class/table

(one-to-many case). In the case of the many-to-many relationship, if the parent is deleted, the entries in the intermediate table for that entity must also be deleted also. In the case of the one-to-many relationship, if the parent is deleted, the foreign key entry (ie. 'owner') must be cleared.

如果我们暂时忽略的OID的问题，一个弱的聚合关系可以用一个中间表（多对多的情况）或者在聚合类/表中建立外键（一对多的情况）来实现。在多对多关系时，如果父对象被删除，在中间表中该实体相关的数据也必须被删除。在一对多关系时，如果父对象被删除，外键对应的实体（即'拥有者'）必须被清除。

In the case of composition, the use of a foreign key is mandatory, with the added constraint that on deletion of the parent the part must be deleted also. Logically there is also the implication with composition that the primary key of the part forms part of the primary key of the whole - for example, a Person's primary key may composed of their identifying documents ID's. In practice this would be cumbersome, but the logical relationship holds true.

在组合的情况下，被强制使用外键，增加父数据被删除部分数据也必须被删除的约束。在逻辑上组合关系也存在部分的主键构成整体的主键的一部分- 例如，人实体的主键由他的唯一识别证明组成。在实践中，这将是十分麻烦的，但逻辑关系是成立的。

9. Define relationship roles

For each association type relationship, each end of the relationship may be further specified with role information. Typically, you will include the Primary Key constraint name and the Foreign Key Constraint name. Figure 6 illustrates this concept. This logically defines the relationship between the two classes. In addition, you may specify additional constraints (eg. {Not NULL}) on the role and cardinality constraints (eg. 0..n).

9.定义关系角色

对于每一个关联关系类型，每个关系的结束需要进一步指定角色信息。通常，您将包括主键约束的名称和外键约束的名称。下图说明了这一概念。这在逻辑上定义了两个类之间的关系。此外，您可以指定其他的限制（如不为空）在角色和基数的限制（如0 .. n）的。

10. Model behaviour

We now come to another difficult issue: whether to map some or all class behaviour to the functional capabilities provided by database vendors in the form of triggers, stored procedures, uniqueness and data constraints, and relational integrity. A non-persistent object model would typically implement all the behaviour required in one

or more programming languages (eg. Java or C++). Each class will be given its required behaviour and responsibilities in the form of public, protected and private methods.

10.行为模型

现在我们讨论另一个棘手的问题：是否需要通过数据库厂商提供的触发器，存储过程，唯一性约束，数据完整性的形式，对部分或全部行为映射到类的功能上。非持久性对象模型通常会实现通过一种或多种程序语言（如Java或C++）实现以上所有行为所需的行为。每个类为它的行为和责任设定公共，受保护和私有方法。

Relational databases from different vendors typically include some form of programmable SQL based scripting language to implement data manipulation. The two common examples are triggers and stored procedures. When we mix the object and relational models, the decision is usually whether to implement all the business logic in the class model, or to move some to the often more efficient triggers and stored procedures implemented in the relational DBMS. From a purely object-oriented point of view, the answer is obviously to avoid triggers and stored procedures and place all behaviour in the classes. This localises behaviour, provides for a cleaner design, simplifies maintenance and provides good portability between DBMS vendors.

来自不同厂商的关系型数据库通常包含一些可编程基于SQL的脚本语言的形式来实现数据操作。有两个常见的例子是触发器和存储过程。当我们混合使用对象和关系模型，通常需要决定所有的业务逻辑是在类模型中实现，还是移到关系数据库DBMS中效率高的触发器和存储过程中实现。从一个纯粹的面向对象角度来看，答案显然是要避免触发器和存储过程，并将所有行为放置在类中。这种本地化的行为，提供了一个更简洁的设计，便于维护和在DBMS供应商之间提供良好的可移植性。

In the real world, the bottom line may be scaling to 100's or 1000's of transactions per second, something stored procedures and triggers are purpose designed for. If purity of design, portability, maintenance and flexibility are the main drivers, localise all behaviour in the object methods.

在现实世界中，底线可能会控制在每秒100次1000次的处理范围之内，会使用一些存储过程和触发器来进行设计的。如果纯设计，便携性，维护性和灵活性为主要驱动力，将把所有行为本地化到对象方法中。

If performance is an over-riding concern, consider delegating some behaviour to the more efficient DBMS scripting languages. Be aware though that the extra time taken to integrate the object model with the stored procedures in a safe way, including issues with remote effects and debugging, may cost more in development time than simply deploying to more capable hardware.

如果关注性能是压倒一切的，可以考虑一些行为使用更有效的DBMS脚本语言。要知道，虽然通过可靠地方式利用额外的时间使对象模型和存储过程统一，问题包括与远程影响和调试的问题，开发成本可能比简单地部署到更强大的硬件环境上更多。

As mentioned earlier, the UML Data Profile provides the following extensions (stereotyped operations) with which you can model DBMS behaviour:

●Primary key constraint (PK);

●Foreign key constraint (FK);

●Index constraint (Index);

●Trigger (Trigger);

●Uniqueness constraint (Unique);

●Validity check (Check).

如前所述，UML数据配置文件提供了以下扩展（原型操作），可以模拟DBMS的行为：

●主键约束(PK);

●外键约束(FK);

●索引约束(Index);

●触发器(Trigger);

●唯一性约束(Unique);

●有效性校验(Check).

11. Produce a physical model

In UML, the physical model describes how something will be deployed into the real world - the hardware platform, network connectivity, software, operating system, dll's and other components. You produce a physical model to complete the cycle - from an initial use case or domain model, through the class model and data models and finally the deployment model. Typically for this model you will create one or more nodes that will host the database(s) and place DBMS software components on them. If the database is split over more than one DBMS instance, you can assign packages (?schema?) of tables to a single DBMS component to indicate where the data will reside.

11.构建物理模型

在UML中，物理模型描述了事物是如何部署到真实世界-硬件平台，网络连接，软件，操作系统，DLL的和其他组件。构建一个物理模型，完成周期-从最初的用例或领域模型，通过类模型和数据模型，最后到部署模型。通常，对于这个模型，你将创建一个或多个节点，包括数据库主机（多个）和DBMS软件组件。如果数据库配置多个DBMS 实例，可以分配包（?schema?）到一个DBMS组件中，用来指示数据将被储存在其中。

Conclusion

This concludes this short article on database modelling using the UML. As you can see, there are quite a few issues to consider when mapping from the object world to the relational. The UML provides support for bridging the gap between both domains, and together with extensions such as the UML Data Profile is a good language for successfully integrating both worlds.

结论

就这样结束了用UML对数据库建模的简短文章。正如你可以看到，从对象世界映射到关系模型还有不少需要考虑的问题。UML提供了两个模型的联系方式，包括扩展，UML 数据模型是可以成功的整合两个模型的非常好的语言。