具有子类型的复合主键表 [英] Compound primary key table with subtypes

问题描述

我和一个数据库架构师正在争论如果一个带有子类型的复合主键的表在关系上是有意义的，并且是一个好的做法。

有两个表Employee和Project。我们创建一个组合表Employee_Project，其中包含一个返回Employee和Project的组合主键。

Employee_Project有没有子类型的有效方法？或者你能想到复合键表可以有子类型的任何情况吗？

对我来说，复合键关系是一个Is A关系（Employee_Project是一个Employee一个专案）。子类型也是一个Is A关系。所以如果你有一个复合键与一个子类型的两个'是A'的关系在一个句子，这让我相信这是一个坏的做法。

解决方案

如果候选子类型是

• 不完全不同，则员工项目具有子类型，但


• 不完全相同

这意味着

• 每个员工项目都有一些共同的
  属性（列）。 不同。


• 某些员工的项目有不同的
  属性。 。

这个决定与共同的和不同的属性有关。它与候选键中的列数没有任何关系。你有没有完全不同但是不完全相同的员工项目？

最常见的业务超类型/子类型示例涉及组织和个人。

• 两者都有地址。


• 两者都有电话号码。 / li>
  
• 在法庭上可以是原告和被告人
  。

• 个人可以上大学。


• 组织可以有首席执行官。 / li>
  
• 个人可以结婚。


• 个人可以有孩子。

因此，您可以将个人和组织表示为称为缔约方的超类型的子类型。

• 缔约方有地址。


• 所有子类型的属性都与父类型相关。

p>同样，这与属于共同的属性以及不同的属性有关。它与候选键中的列数无关。

对我来说，复合键关系是
a'Is A关系
（Employee_Project是一个员工和一个
项目）。

数据库设计师不认为那样。我们根据表的谓词。

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.

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?

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.

解决方案

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.
• 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).

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

这篇关于具有子类型的复合主键表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！