具有子类型的复合主键表

Me and a database architect were having argument over if a table with a compound primary key with subtypes made sense relationally and if it was a good practice.

我和一个数据库架构师正在讨论一个具有复合主键和子类型的表是否有关系,并且这是一个好习惯。

Say we have two tables Employee and Project. We create a composite table Employee_Project with a composite primary key back to Employee and Project.

假设我们有两个表Employee和Project。我们创建一个复合表Employee_Project,其中一个复合主键返回Employee和Project。

Is there a valid way for Employee_Project to have subtypes? Or can you think of any scenario where a composite key table can have subtypes?

Employee_Project是否有一种有效的方式来获得子类型?或者您能想到复合键表可以具有子类型的任何场景吗?

To me a composite key relationship is a 'Is A' relationship (Employee_Project is a Employee and a Project). Subtypes are also a 'Is A' relationship. So if you have a composite key with a subtype its two 'Is A' relationships in one sentence which makes me believe this is a bad practice.

对我来说,复合键关系是'Is A'关系(Employee_Project是一个Employee和一个Project)。子类型也是'是A'的关系。因此,如果你有一个带有子类型的复合键,它在一个句子中有两个'是A'关系,这让我相信这是一个不好的做法。

3 个解决方案

#1

Employee projects have subtypes if the candidate subtypes are

如果候选子类型是,则员工项目具有子类型

• not utterly different, but

没有完全不同,但是

• not exactly alike

不完全相同

That means that

这意味着

• Every employee project has some attributes (columns) in common. So they're not utterly different.

每个员工项目都有一些共同的属性(列)。所以他们并没有完全不同。

• Some employee projects have different attributes than others. So they're not exactly alike.

某些员工项目的属性与其他项目不同。所以他们并不完全相同。

The determination has to do with common and distinct attributes. It doesn't have anything to do with the number of columns in a candidate key. Do you have employee projects that are not utterly different, but not exactly alike?

该决定与共同和不同的属性有关。它与候选键中的列数无关。您是否有完全不同但不完全相同的员工项目?

The most common business supertype/subtype example concerns organizations and individuals. They're not utterly different.

最常见的业务超类型/子类型示例涉及组织和个人。他们并没有完全不同。

• Both have addresses.

两者都有地址。

• Both have phone numbers.

两者都有电话号码。

• Both can be plaintiffs and defendants in court.

两者都可以是法庭上的原告和被告。

But they're not exactly alike.

但他们并不完全相同。

• Individuals can go to college.

个人可以上大学。

• Organizations can have a CEO.

组织可以拥有一名CEO。

• Individuals can get married.

个人可以结婚。

• Individuals can have children.

个人可以生孩子。

• Organizations (in the USA) can be liquidated.

组织(在美国)可以清算。

So you can express individuals and organizations as subtypes of a supertype called, say, "Parties". The attributes all the subtypes have in common relate to the supertype.

因此,您可以将个人和组织表达为称为“缔约方”的超类型的子类型。所有子类型的共同属性都与超类型有关。

• Parties have addresses.

缔约方有地址。

• Parties have phone numbers.

派对有电话号码。

• Parties can be plaintiffs and defendants in court.

当事人可以在法庭上成为原告和被告。

Again, this has to do with attributes that are held in common, and attributes that are distinct. It has nothing to do with the number of columns in a candidate key.

同样,这与共同拥有的属性和不同的属性有关。它与候选键中的列数无关。

To me a composite key relationship is a 'Is A' relationship (Employee_Project is a Employee and a Project).

对我来说,复合键关系是'Is A'关系(Employee_Project是一个Employee和一个Project)。

Database designers don't think that way. We think in terms of a table's predicate.

数据库设计者不这么认为。我们根据表的谓词来思考。

#2

Employee-project is a bit hard, but one can imagine something like this -- although I'm not much of a chemist.

员工项目有点困难,但人们可以想象这样的事情 - 虽然我不是化学家。

Or something like this, which would require different legal forms (fields) for single person ownership vs joint (time-share).

或类似的东西,这需要不同的法律形式(领域)单人所有权与联合(时间分享)。

Or like this, providing that different forms are needed for full time and temp.

或者像这样,提供全时和临时需要的不同形式。

#3

If an employee can have many projects and a project can have many employees it is a many-to-many join that RDBM's can only represent easily in one way (the way you have outlined above.) You can see in the ER diagram below (employee / departments is one of the classic many-to-many examples) that it does not have a separate ER component. The separate table is a leaky abstraction of RDBMS's (which is probably why you are having a hard time modeling it).

如果一个员工可以有很多项目,而一个项目可以有很多员工,那么RDBM只能以一种方式轻松表示多对多联接(就像上面概述的那样)。你可以在下面的ER图中看到(员工/部门是经典的多对多示例之一,它没有单独的ER组件。单独的表是RDBMS的漏洞抽象(这可能是你很难对其进行建模的原因)。

http://www.library.cornell.edu/elicensestudy/dlfdeliverables/fallforum2003/ERD_final.doc

Bridge Entities

When an instance of an entity may be related to multiple instances of another entity and vice versa, that is called a “many-to-many relationship.” In the example below, a supplier may provide many different products, and each type of product may be offered by many suppliers:

当实体的实例可能与另一个实体的多个实例相关时,反之亦然,这称为“多对多关系”。在下面的示例中,供应商可能提供许多不同的产品,以及每种类型的产品可能由许多供应商提供:

While this relationship model is perfectly valid, it cannot be translated directly into a relational database design. In a relational database, relationships are expressed by keys in a table column that point to the correct instance in the related table. A many-to-many relationship does not allow this relationship expression, because each record in each table might have to point to multiple records in the other table.

虽然这种关系模型完全有效,但它无法直接转换为关系数据库设计。在关系数据库中,关系由表列中的键表示,该列指向相关表中的正确实例。多对多关系不允许此关系表达式,因为每个表中的每个记录可能必须指向另一个表中的多个记录。

http://users.csc.calpoly.edu/~jdalbey/205/Lectures/ERD_image004.gif

Here they do not event bother with a separate box although they add in later (at this step it is a 'pure' ER diagram). It can also be explicitly represented with a box and a diamond superimposed on each other.

虽然他们稍后添加(在此步骤它是一个'纯'ER图),但他们不会因为单独的框而烦恼。它也可以用框和菱形相互叠加来明确表示。