6NF中引用完整性的复合键和代理键

Question

收集三层信息：

该层包含具有UNIQUE自然索引的数据和易于传输的代理密钥。

Table Surnames:

+-----------------------------+--------------+
|    ID (Auto Increment, PK)  |    Surname   |
+-----------------------------+--------------+
|               1             |     Smith    |
|               2             |    Edwards   |
|               3             |     Brown    |
+-----------------------------+--------------+

Table FirstNames

+-----------------------------+--------------+
|   ID (Auto Increment, PK)   |   FirstName  |
+-----------------------------+--------------+
|               1             |     John     |
|               2             |     Bob      |
|               3             |     Mary     |
|               4             |     Kate     |
+-----------------------------+--------------+

自然键

或者，ID如Mike
Sherrill所解释的，上面的两个表可以不存在，并且可以使用Surname和FirstName作为自然主键。在这种情况下，假设参考下面的图层varchar而不是int。

第2层：人

在这一层中，使用了复合索引。该值可以是UNIQUE或PRIMARY，取决于是否将替代键用作主键。

+-----------------+--------------+
|    FirstName    |    LastName  |
+-----------------+--------------+
|        1        |       2      |
|        1        |       3      |
|        2        |       3      |
|        3        |       1      |
|        4        |       2      |
|       ...       |      ...     |
+-----------------+--------------+

第三层：父母

在这一层中，通过ParentsOf表探索人与人之间的关系。

ParentsOf

+-----------------+-----------------+
|      Person     |   PersonParent  |
+-----------------+-----------------+

 OR

+-----------------+-----------------+-----------------+-----------------+
| PersonFirstName |  PersonSurname  | ParentFirstName |  ParentSurname  |
+-----------------+-----------------+-----------------+-----------------+

问题

假设引用完整性对我来说非常重要，并且我将拥有FOREIGN KEYS这些索引，以便我可以在这方面让数据库负责监视其自身的完整性，并且如果我要使用ORM，它将像Doctrine这样的人，它对复合主键具有本地支持…

请帮助我了解：

• 在第一层上使用代理密钥与自然密钥进行权衡的列表。

• 在第二层利用复合密钥与代理密钥进行权衡的清单，这些密钥可以转移到第三层。

我对听到更好的消息不感兴趣，因为我知道专业人员之间在该主题上存在重大分歧，这将引发一场宗教战争。相反，我要问的是，尽可能简单和客观地实现，这是通过将代理密钥传递给每个图层而不是保持主密钥（自然/复合或代理/复合）来实现的。任何人都可以在SO和其他网站上找到说
永远不 使用或 永远 使用代理密钥的人。相反，在您的回答中，我将最欣赏折衷的合理分析。

编辑：
已经指出，姓氏示例对于使用6NF而言是一个较差的示例。为了使问题完整无缺，我将保留它。如果您在构想此用例时遇到困难，最好使用“杂货项目”列表。又名：

+-----------------------------+--------------+
|   ID (Auto Increment, PK)   |   Grocery    |
+-----------------------------+--------------+
|               1             | Sponges      |
|               2             | Tomato Soup  |
|               3             | Ice Cream    |
|               4             | Lemons       |
|               5             | Strawberries |
|               6             | Whipped Cream|
+-----------------------------+--------------+

+-----------------------------+--------------+
|   ID (Auto Increment, PK)   |   Brand      |
+-----------------------------+--------------+
|               1             | Bright       |
|               2             | Ben & Jerry's|
|               3             | Store Brand  |
|               4             | Campbell's   |
|               5             | Cool Whip    |
+-----------------------------+--------------+

自然复合关键示例：

+-----------------------------+--------------+
|           Grocery           |   Brand      |
+-----------------------------+--------------+
|           Sponges           | Bright       |
|           Ice Cream         | Ben & Jerry's|
|           Ice Cream         | Store Brand  |
|           Tomato Soup       | Campbell's   |
|           Tomato Soup       | Store Brand  |
|           Lemons            | Store Brand  |
|           Whipped Cream     | Cool Whip    |
+-----------------------------+--------------+

推荐配对

+-----------------+-----------------+-----------------+-----------------+
|     Grocery1     |  Brand1        | Grocery2        |  Brand2         |
+-----------------+-----------------+-----------------+-----------------+

重申一下，这也只是一个例子。这不是我建议继续进行的方式，但是应该可以帮助说明我的问题。

该方法存在不足之处。我要重申的是，这个问题是要求您逐步了解以下每种方法的优缺点，而不是强调一种方法优于另一种方法。我相信大多数人能够超越这个特定示例的可疑性质来回答核心问题。此编辑适用于那些无法编辑的内容。

下面有一些很好的答案，如果您对该走的方向感到好奇，请阅读它们。

结束编辑

谢谢！

Answer 1

这里有一些权衡：

单一代理人为（人工创建）：

• 所有子表外键只需要一个列即可引用主键。

• 非常容易更新表中的自然键，而无需使用外键更新每个子表

• 较小的主键/外键索引（即，非宽键索引）可以使数据库运行更快，例如，当删除父表中的记录时，需要搜索子表以确保不会创建孤儿。窄索引的扫描速度较快（仅凭外观即可）。

• 您将拥有更多索引，因为您很可能还希望对数据中存在的任何自然键进行索引。

自然组合键表：

• 数据库中的索引更少

• 数据库中的列较少

• 更容易/更快地插入大量记录，因为您无需抓住序列生成器

• 更新化合物中的键之一要求还更新每个子表。

然后是另一类：人工合成主键

我只发现了一个有意义的实例。当您需要标记每个表中的每个记录以提高行级安全性时。

例如，假设您有一个数据库，该数据库存储了50,000个客户端的数据，而每个客户端都不应该看到其他客户端的数据-这在Web应用程序开发中很常见。

如果每个记录都用client_id字段标记，则您正在创建行级安全性环境。大多数数据库都有正确设置时可以强制执行行级安全性的工具。

首先要做的是设置主键和外键。通常，具有以id字段为主键的表。通过添加client_id密钥，现在是复合密钥。并且有必要携带client_id到所有子表。

组合密钥基于2个代理密钥，是一种防弹方式，可确保客户端之间以及整个数据库内的数据完整性。

此后，您将创建视图（或使用Oracle EE设置虚拟专用数据库）或其他各种结构，以允许数据库强制执行行级安全性（这是其所有主题）。

可以肯定的是，此数据结构不再归一化为n级。client_id每个pk /
fk中的字段会反规范化原本正常的模型。该模型的好处是易于在数据库级别强制执行行级别的安全性（这是数据库应该执行的操作）。每次选择，插入，更新，删除都限于client_id当前设置的会话。该数据库具有
会话意识 。

概括

替代键始终是安全的选择。他们需要更多的工作来设置并需要更多的存储空间。

我认为最大的好处是：

• 能够立即更改一个表和所有其他子表中的PK，而无需任何操作。

• 当数据混乱时-有时由于编程错误，代理密钥会使清理变得容易得多，在某些情况下，由于存在代理密钥，因此只能进行清理。

• 由于数据库能够搜索属性以找到s.key，然后通过单个数字键联接所有子表，因此查询性能得到了改善。

自然键（尤其是复合NKey）使编写代码变得很痛苦。当您需要连接4个表时，“ where子句”将比使用单个SKey时更长（并且更容易弄乱）。

代理密钥是“安全”的途径。自然键在某些地方很有用，我会说数据库中大约1％的表。