选择最佳的主键+编号系统

Question

选择最佳的主键+编号系统

databasedatabase-designprimary-key

24

我们正在为创建的资产系统设计编号系统，这个话题在办公室引起了一些激烈的讨论，因此我决定向SO的专家请教。

考虑下面的数据库设计，哪种选项更好。

alt text

示例1：使用自动代理键。

=================   ==================
Road_Number(PK)     Segment_Number(PK)
=================   ==================
 1                   1

示例2：使用程序生成的主键

=================   ==================
Road_Number(PK)     Segment_Number(PK)
=================   ==================
 "RD00000001WCK"     "00000001.1"

(00000001.1表示这是该路段的第一个部分。每次添加新的部分时，此数字会递增，例如：00000001.2)

示例3: 同时使用两种方法（添加新列）

=======================    ==========================
ID(PK) Road_Number(UK)     ID(PK)  Segment_Number(UK)
=======================    ==========================
 1     "RD00000001WCK"       1       "00000001.1"

背景信息：我们将在报告和其他文档中使用“Road Number”和“Segment Number”，因此它们必须是唯一的。

我一直喜欢保持简单，所以我更喜欢示例1，但我已经读到过不应该在报告/文档中公开主键。所以现在我更倾向于示例3。

我也倾向于示例3，因为如果我们决定更改资产编号生成方式，就不必对主键进行级联更新。

你认为我们应该怎么做？

谢谢。

编辑：感谢大家的回答，对我帮助很大。

- Nathan W

13个回答

7

我个人认为保持简单并使用自增主键是最好的选择。如果你需要在程序中显示更“可读”的内容，那么可能需要考虑其他想法，但我认为这只会给主键字段增加不必要的复杂性。

- TheTXI

让键只是键。这样，您就不必担心更改类型、添加列或其他任何事情。如果需要快速查找其值，请在其他列上使用索引。 - Jason Cohen

我同意。把事情搞得复杂只会让事情更加复杂。 - TheTXI

7

我非常坚定地支持“不要将主键用作有意义的数据”的观点。每当我违反这一政策时，结果总是令人痛心的。迟早有一天，有意义的数据需要更改，如果这意味着您必须更改主键，那么可能会变得痛苦。主键很可能会用于外键约束，您可能需要花费很长时间来解决所有问题，以便进行简单的数据更改。

我在创建的每个表中始终使用GUID / UUID作为主键，但这只是个人喜好，序列号等也很好。

- sipwiz

4

在PK字段中不要加入任何含义，除非：

该值绝对永远不会改变；
没有两个人会因为使用特定行的某个值而合理争论。

选择第一种选项，并在应用程序中格式化该值以在显示时呈现第二或第三种选项的外观。

- JohnFx

你能设计一个数据库，使得任何人都无法更改其中的值吗？ - JeffO

这有点奇怪的问题。我认为你误解了我的回答。我的意思是，你会想要选择一个没有人想要更改的PK，而不一定是它在物理上不可能更改。 - JohnFx

3

我认为需要记住的重要事情是，在您的数据库/设计中，每个表可能有多个键。这些称为候选键。请查看候选键的维基百科条目按照定义，所有候选键都是平等的。它们都是所讨论的表的唯一标识符。

然后，您的工作就是从候选键池中选择最好的候选人作为主键。其他表将使用主键来建立关系约束，但你仍可以继续使用候选键查询表。

由于主键被其他结构引用，因此在连接操作中使用，所以我对主键选择的标准如下（按重要性排列）：

不变/稳定 - 主键值不应更改。如果更改，您可能会引入更新异常。
非空 - 大多数DBMS平台要求主键属性不能为空
简单 - 简单的数据类型和值可用于物理存储和性能。整数值在这里很有效，并且是大多数代理/自动生成键的首选数据类型。

一旦您已经确定了候选键，上述标准可以用来选择主键。如果没有“自然”的候选键符合标准，那么可以创建并使用符合标准的代理键，就像其他答案中提到的一样。

- jwolly2

1

遵循“不使用”政策。

你可能会遇到一些问题：

你需要从多个主机生成密钥。

有人想要保留连续的数字以便一起使用。

人们希望它有多有意义？为此，战争已经爆发，而你已经参与了第一次小规模的战斗。“它已经很有意义了，如果我们只添加两个数字，我们就可以……”也就是说，你正在建立一个应该可扩展的设计风格。

如果你正在连接这两个，那么你正在进行类型转换，这可能会破坏你的查询优化器。

你需要重新分类道路，并重新定义它们的边界（即移动道路），这意味着更改主键，可能会丢失链接。

所有这些都有解决方法，但这是一种解决方法会越来越多并失去控制的问题。而且只需要几个问题就可以超出“简单”的范围。

- le dorfier

1

如前所述，将内部主键保持为键，无论平台上最优的数据类型是什么。

然而，您确实需要让编号系统的争论得到解决，因为这实际上是业务需求，也许我们可以称之为资产识别系统。

如果只有一个标识符，那么将其作为列添加到主表中。如果可能会有许多识别系统（并且资产通常有很多），则需要另外两个表

    标识符类型表             标识符交叉引用表
      类型ID             ------------> 类型ID              (唯一
      类型名称                         标识符字符串     键)
                                        内部ID

这样，需要访问资产的不同人员可以以自己的方式进行识别。例如，服务器团队将以与网络团队不同的方式识别服务器，并且与项目管理、财务等人员也不同。

此外，您还可以参加所有人互相争论的会议。

- wentbackward

0

另一件需要记住的事情是，如果您将大量数据导入此系统，则可能会发现像 Road_Number 这样的内容并不像您想象的那么独特，而且可能存在解决问题的操作障碍（重新绘制道路标志等）。

- MadCoder

0

虽然自然键对业务用户来说可能具有很大的意义，但如果您没有达成协议，认为这些键是神圣的，不应更改，那么在维护数据库时，您很可能会抓狂，因为“产品代码必须更改以适应公司收购的新产品线”。您需要保护数据的RI，并且使用自增的整数作为主键是最好的选择。在索引和遍历整数列时，性能也更好。

虽然自然键不适合作为主键，但非常适合用户消费，您可以通过索引强制执行唯一性。它们为数据带来了上下文，使所有参与方更容易理解。此外，在需要重新加载数据时，自然键可以帮助验证您的查找仍然有效。

- David Robbins

0

我会选择代理键，但是您可能希望有一个计算列来将代理键“格式化”成更“可读”的值，如果这可以提高您的报告效果。例如，计算列可以根据代理键生成示例2，以显示目的。

我认为代理键路线是正确的选择，我唯一允许使用其他方法的情况是在连接表中，其中主键可以由外键引用组成。即使在这些情况下，我发现拥有代理主键比没有更有用。

- tvanfosson

我同意，但是在查询中连接一些列以生成报告中的“可读”键是否可能呢？ - Damien

是的，但这正是计算列的作用。如果您始终以编程方式访问它，则可能几乎没有区别，但任何手动查询都将始终获取该值，并且不必担心定义错误。 - tvanfosson

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- cletus · Accepted Answer

这实际上是一篇关于代理（也称为技术或合成）与自然主键的讨论，这个主题已经被广泛涵盖。我在 Database Development Mistakes Made by AppDevelopers 中涵盖了该主题。

自然键是基于外部有意义数据的键，这些数据据说是唯一的。常见的例子包括产品代码、两个字母的州代码（美国）、社会安全号码等。代理或技术主键是那些在系统外完全没有意义的主键。它们仅仅为了识别实体而发明，并且通常是自递增字段（SQL Server、MySQL等）或序列（尤其是Oracle）。

在我看来，你应该总是使用代理键。这个问题已经在以下问题中出现：

自动编号字段是最好的选择。如果你的键在数据库外具有意义（如资产编号），那么这些键很可能会发生改变，更改键是有问题的。只需将这些信息作为索引插入相关表格即可。