SQL 我们如何实施 IS-A 关系？

How do we implement an IS-A Relationship?

提问by athspk

We implement an One-to-Many relationship by adding one Table's PK, as FK to the other Table. We implement a Many-to-Many relationship by adding 2 Table's PKs to a third Table.

我们通过将一个表的 PK 作为 FK 添加到另一个表来实现一对多关系。我们通过将 2 个表的 PK 添加到第三个表来实现多对多关系。

How do we implement an IS-A Relationship ?

我们如何实现 IS-A 关系？

The Entities are TECHNICIAN and ADMINISTRATIVE which both are EMPLOYEE. I could just use an extra field in the Table EMPLOYEE(id, name, surname, role, ...AdminFields..., ...TechFields...)

实体是技术人员和行政人员，两者都是雇员。我可以在表 EMPLOYEE(id, name, surname, role, ...AdminFields..., ...TechFields...) 中使用一个额外的字段

but i would like to explore the IS-A option.

但我想探索 IS-A 选项。

EDIT: I did as Donnie suggested, but without the role field.

编辑：我按照唐尼的建议做了，但没有角色字段。

采纳答案by athspk

I did as Donnie suggested, but without the rolefield, because it complicates things. This is the final implementation:

我按照唐尼的建议做了，但没有角色字段，因为它使事情复杂化。这是最终的实现：

DDL:

DDL：

CREATE TABLE Employee (
ast VARCHAR(20) not null,
firstname VARCHAR(200) not null,
surname VARCHAR(200) not null,
...
PRIMARY KEY(ast)
);

CREATE TABLE Administrative (
employee_ast VARCHAR(20) not null REFERENCES Employee(ast),
PRIMARY KEY(employee_ast)
);

CREATE TABLE Technical (
employee_ast VARCHAR(20) not null REFERENCES Employee(ast),
...
PRIMARY KEY(employee_ast)
);

ER Diagram:

ER图：

In this model there are no Employees of Generic Type. Here, an Employee can only be Administrative or Technical.

在此模型中，没有通用类型的员工。在这里，员工只能是管理人员或技术人员。

回答by Walter Mitty

The IS-A relationship is also known as the gen-spec design pattern. Gen-spec is short for "generalization specialization".

IS-A 关系也称为 gen-spec 设计模式。Gen-spec 是“泛化专业化”的缩写。

Relational modeling of gen-spec is different from object modeling of gen-spec because the relational model doesn't have inheritance built in.

gen-spec 的关系建模与 gen-spec 的对象建模不同，因为关系模型没有内置继承。

Here is a good article that shows how to implement gen-spec as a collection of tables.

这是一篇很好的文章，展示了如何将 gen-spec 实现为表的集合。

http://www.javaguicodexample.com/erdrelationalmodelnotes1.html

http://www.javaguicodexample.com/erdrelationalmodelnotes1.html

Pay particular attention to the way primary keys are set up in the specialized tables. That's what makes using these tables so easy.

请特别注意在专用表中设置主键的方式。这就是使用这些表如此容易的原因。

You can find lots of other articles by googlin "generalization specialization relational modeling".

您可以在 googlin“泛化专业化关系建模”中找到许多其他文章。

回答by Donnie

I've always done this with a rolefield, and then optional relationships.

我总是用一个role字段来做这个，然后是可选的关系。

I.e., table EMPLOYEE (id, ...generic fields... , role)

即表 EMPLOYEE (id, ...generic fields... , role)

And then, for each role:

然后，对于每个角色：

table ROLE1 (employeeid, ...specific fields...)

桌子 ROLE1 (employeeid, ...specific fields...)

This allows you to get general employee information with a single query, and requires joins to get at the role-specific information. The one (bigish) downside to this is if you need one super-report with all of the role information on it you get stuck with a bunch of outer joins.

这允许您通过单个查询获取一般员工信息，并且需要联接以获取特定于角色的信息。这样做的一个（最大的）缺点是，如果您需要一个包含所有角色信息的超级报告，您就会陷入一堆外部联接的困境。

回答by Don Roby

If you have an OO application that you need to connect to a relational back-end database, I'd recommend getting Martin Fowler's Patterns of Enterprise Application Architecture.

如果您有一个需要连接到关系后端数据库的 OO 应用程序，我建议您阅读 Martin Fowler 的企业应用程序架构模式。

He also has some relevant notes and diagrams on his website. Specifically, the patterns Single Table Inheritance, Class Table Inheritanceand Concrete Table Inheritancedescribe three tactics for mapping IS-A in data tables.

他的网站上还有一些相关的注释和图表。具体来说，Single Table Inheritance、Class Table Inheritance和Concrete Table Inheritance 模式描述了在数据表中映射 IS-A 的三种策略。

If you're using Hibernate or JPA, they support mappings for all of these, though they have different names for them.

如果您使用的是 Hibernate 或 JPA，它们都支持所有这些的映射，尽管它们的名称不同。

In this specific instance, I wouldn't use IS-A at all though.

在这个特定的例子中，我根本不会使用 IS-A。

Things like employee roles are better modeled as HAS-A, as

像员工角色这样的事情最好建模为 HAS-A，因为

1. You might want a single person to have multiple roles.
2. Changing a person's role will be easier.

1. 您可能希望一个人担任多个角色。
2. 改变一个人的角色会更容易。

回答by Ronnis

This paper describes some strategies for mapping generalizations to into schema design.

本文描述了将泛化映射到模式设计的一些策略。

http://www.sztaki.hu/conferences/ADBIS/3-Eder.pdf

http://www.sztaki.hu/conferences/ADBIS/3-Eder.pdf

A copy of the abstract:

摘要副本：

The richer data models of object relational databases opens many more options for the logical design of a database schema increasing the complexity of logical database design enormously. Focusing on generalization constructs of conceptual models we explore the performance implications of the various design alternatives for mapping generalizations into the schema of an object-relational database system.

对象关系数据库更丰富的数据模型为数据库模式的逻辑设计提供了更多的选择，极大地增加了逻辑数据库设计的复杂性。专注于概念模型的泛化构造，我们探索了将泛化映射到对象关系数据库系统模式的各种设计备选方案的性能影响。

回答by mattmc3

Why not implement this as a one-to-zero/one table relationship? Let's say you have a table representing a base class called Vehicle, with a primary key of VehicleID. Then, you can have any number of satellite tables representing all the subclasses of Vehicle, and those tables also have VehicleID as their primary key, having a 1->0/1 relationship from Vehicle->Subclass.

为什么不将其实现为一对零/一个表关系？假设您有一个表，表示名为 Vehicle 的基类，主键为 VehicleID。然后，您可以有任意数量的卫星表来表示 Vehicle 的所有子类，并且这些表也将 VehicleID 作为它们的主键，从 Vehicle->Subclass 具有 1->0/1 关系。

Or, if you want to make it simpler and you know for sure that you'll only ever have a few sub classes and there's not much chance of that changing, you could just represent the whole structure in a single table with a discriminator type field.

或者，如果你想让它更简单，并且你确定你只会有几个子类并且改变的可能性不大，你可以在一个带有鉴别器类型字段的表中表示整个结构.

回答by MacGyver

It depends if you are building a mono-hierarchy or poly-hierarchy. This is a hard coded design, which I believe is what you wanted.

这取决于您是在构建单一层次结构还是多层次结构。这是一个硬编码设计，我相信这是您想要的。

For mono (child table has one parent table), where child IS-A parent, the FK and PK is the same in the child table, and that key is also the PK in the parent table.

对于mono（子表有一个父表），其中child IS-A parent，子表中的FK和PK是一样的，那个key也是父表中的PK。

For poly (child table has multiple parent tables), where child IS-A parent-1 and child IS-A parent-2, you'll have a composite key (meaning multiple primary keys to make table record unique), where the rule is the same as a mono-hierarchy for each key.

对于poly（子表有多个父表），其中子IS-A parent-1和子IS-A parent-2，您将有一个复合键（意味着多个主键使表记录唯一），其中规则与每个键的单一层次结构相同。

回答by tvanfosson

Most ORMs implement the IS-A relationship using a single column discriminator, choosing which subclass to instantiate based on the value in a particular column. With respect to your example, you probably don't really mean role, since typically a person can fill many different types of roles. Roles would typically be modeled as a has-arelationship. If you do try to implement it using is-arelationships (or subclassing) you inevitably end up having to do something more complicated to handle cases where you have a person filling a hybrid position -- i.e., a secretary who also functions as the local IT person, needing permissions or attributes of both.

大多数 ORM 使用单个列鉴别器实现 IS-A 关系，根据特定列中的值选择要实例化的子类。关于您的示例，您可能并不是真正的意思role，因为通常一个人可以担任许多不同类型的角色。角色通常会被建模为一种关系。如果您确实尝试使用is-a关系（或子类化）来实现它，那么您不可避免地最终不得不做一些更复杂的事情来处理您有一个人担任混合职位的情况——即，同时担任本地秘书的秘书IT 人员，需要两者的权限或属性。