ID design and primary keys / ID 设计与主键

Author: Alexey Makhotkin squadette@gmail.com, (~2300 words) 作者： Alexey Makhotkin squadette@gmail.com, (约 2300 字)

This is the first part of a systematic discussion of primary keys in database design. As usual, we present the material in a way that deviates from the traditional approach. This is basically a bonus chapter from the “Database Design Book”. The goal of this text is to teach you how to design your primary keys based on business requirements. 这是关于数据库设计中主键系统性讨论的第一部分。像往常一样，我们以一种偏离传统方法的方式呈现这些内容。这基本上是《数据库设计书》（Database Design Book）中的一个额外章节。本文的目标是教你如何根据业务需求来设计主键。

In part 1, we begin at the logical level. We introduce the idea of external IDs. We discuss anchor IDs and their requirements; particularly, when anchor IDs can be used as external IDs. Then we talk about handling external IDs generated by outside systems; particularly, which external IDs could be used as anchor IDs; Then we move to the physical level. We discuss the idea of a primary key, fully dissociated from its business meaning; Then we discuss a simple use case: a tiny content management system that demonstrates a simple anchor table design with integer primary key; Additionally, we talk about uniqueness constraints and their relationship with external IDs; Table of contents In part 2, we’ll discuss composite primary keys and how they are used in database design. Subscribe here to receive updates: 在第一部分中，我们从逻辑层面开始。我们引入了外部 ID 的概念。我们讨论了锚点 ID（anchor IDs）及其要求；特别是锚点 ID 何时可以用作外部 ID。然后，我们讨论如何处理由外部系统生成的外部 ID；特别是哪些外部 ID 可以用作锚点 ID；接着，我们转向物理层面。我们讨论了与业务含义完全脱钩的主键概念；然后讨论了一个简单的用例：一个微型内容管理系统，演示了带有整数主键的简单锚点表设计；此外，我们还讨论了唯一性约束及其与外部 ID 的关系。目录：在第二部分中，我们将讨论复合主键及其在数据库设计中的应用。点击此处订阅以获取更新：

External IDs / 外部 ID

Let’s forget for a moment about databases, tables, primary keys and other things that exist on a physical level. We need to focus first on business requirements, and on the logical model that could be extracted from them. In many business-oriented systems some entities need to have a unique identifier. Some examples: spare parts may have one or more part numbers; people have a unique taxpayer identification number, such as SSN in the United States or BSN in the Netherlands; pages in a content management system can have a URL such as /about, or just /content.php?id=25; ticket-tracking systems use familiar strings such as FOOBAR-123; etc., etc. Let’s call such unique identifiers external IDs. They could be used externally: sent in an email, printed on a piece of paper, told over the phone. 让我们暂时忘记数据库、表、主键以及其他存在于物理层面的事物。我们首先需要关注业务需求，以及可以从中提取的逻辑模型。在许多面向业务的系统中，某些实体需要拥有唯一的标识符。例如：备件可能有一个或多个零件编号；个人拥有唯一的纳税人识别号，例如美国的 SSN 或荷兰的 BSN；内容管理系统中的页面可以拥有 URL，如 /about 或 /content.php?id=25；工单跟踪系统使用熟悉的字符串，如 FOOBAR-123 等。我们将这些唯一标识符称为外部 ID。它们可以在外部使用：通过电子邮件发送、打印在纸上或通过电话告知。

External IDs have three defining properties: 外部 ID 具有三个定义属性：

1. External IDs uniquely identify an entity: there is exactly one entity corresponding to each external ID. The opposite may not always be true: a single entity may have no external ID, one external ID, or more. For example, many children do not have a passport. Passports also can be reissued, but we can identify a person by their old passport number.

2. 外部 ID 唯一标识一个实体：每个外部 ID 对应且仅对应一个实体。反之则不一定成立：单个实体可能没有外部 ID、有一个或多个外部 ID。例如，许多儿童没有护照。护照也可以补发，但我们可以通过旧护照号码识别一个人。

3. External IDs can change. Alright, we need to make the first property more precise: “at any given moment there is exactly one entity corresponding to each external ID“. For example, you may want to rename your social media handle, and somebody else can grab your old one. So, user @alice today may be a different Alice later.

4. 外部 ID 可能会改变。好吧，我们需要更精确地定义第一个属性：“在任何给定时刻，每个外部 ID 对应且仅对应一个实体”。例如，你可能想更改社交媒体账号，而其他人可能会抢注你旧的账号。因此，今天的用户 @alice 在未来可能是另一个 Alice。

5. There could be more than one type of external ID for an entity. For example, if we sell spare parts on Amazon, they would have both part numbers (assigned by vendor) and ASIN (assigned by Amazon).

6. 一个实体可能有多种类型的外部 ID。例如，如果我们通过亚马逊销售备件，它们既有零件编号（由供应商分配），也有 ASIN（由亚马逊分配）。

Anchor IDs / 锚点 ID

Now we can remember again that we have a database, but it’s still a bit too early to talk about tables and primary keys. In the “Database Design Book” we use the term “anchors”. Anchors are mostly like entities, but we don’t like the word “entity” because it is too ambiguous. Anchor ID is required for reliable and unambiguous identification of anchor instances. 现在我们可以重新想起我们有数据库，但谈论表和主键还为时过早。在《数据库设计书》中，我们使用“锚点”（anchors）这个术语。锚点在很大程度上类似于实体，但我们不喜欢“实体”这个词，因为它太模糊了。锚点 ID 是可靠且明确地识别锚点实例所必需的。

Suppose that we maintain a database of books, and there are 100 titles in our database. We need a way to identify each of the 100 books in such a way that every book has an anchor ID, and every anchor ID corresponds to exactly one book. We cannot use ISBN, because some books do not have ISBN. We cannot use the title: maybe we have five different Bibles in our collection, and so on. A common solution for this problem is to use integer numbers, starting from 1, 2, 3, and so on. So we’d have a book with ID=1, a book with ID=2, and so on. We could use such integer numbers in the actual database tables. They do not have their own meaning. 假设我们维护一个图书数据库，其中有 100 本书。我们需要一种方法来识别这 100 本书中的每一本，使得每本书都有一个锚点 ID，且每个锚点 ID 对应且仅对应一本书。我们不能使用 ISBN，因为有些书没有 ISBN。我们不能使用书名：也许我们的收藏中有五本不同的《圣经》，等等。解决这个问题的一个常见方案是使用从 1、2、3 等开始的整数。因此，我们会有一本 ID=1 的书，一本 ID=2 的书，以此类推。我们可以在实际的数据库表中使用这些整数。它们本身没有含义。

An additional requirement for the anchor ID is that it is immutable: its value never changes. Meaningless integer numbers satisfy this requirement because you just never need to change them: ID=2 is no better or worse than ID=3. Simple integer numbers are the most common solution, but sometimes we have other options: unique strings such as “fr” or “CHF”; tuples: a combination of two or more integers or strings; UUIDs, though we can treat them as just big non-sequential integers; We’ll discuss such scenarios later in this series of posts. 锚点 ID 的另一个要求是不可变性：其值永远不会改变。无意义的整数满足这一要求，因为你根本不需要更改它们：ID=2 并不比 ID=3 好或坏。简单的整数是最常见的解决方案，但有时我们还有其他选择：唯一的字符串，如“fr”或“CHF”；元组：两个或多个整数或字符串的组合；UUID，尽管我们可以将其视为大的非顺序整数；我们将在本系列文章的后续部分讨论这些场景。

Anchor IDs as external IDs / 作为外部 ID 的锚点 ID

Any anchor ID could in principle be used as an external ID, and this often does happen. Sometimes, however, this is undesirable. Consider an e-commerce system, with users placing orders. Each order has an order ID. Most certainly, we have an “orders” table that has an “orders.id” column, that contains auto-incremented integer IDs. Can we use those numbers in confirmation emails etc.? Technically we can, but this creates a possibility of industrial espionage. Our competitors can analyze how quickly the sequential number grows, by periodically making an order. This allows them to track your business results, and you may not want this. 原则上，任何锚点 ID 都可以用作外部 ID，这种情况也经常发生。然而，有时这是不可取的。考虑一个电子商务系统，用户在其中下单。每个订单都有一个订单 ID。我们肯定有一个包含“orders.id”列的“orders”表，其中包含自增的整数 ID。我们可以在确认邮件等中使用这些数字吗？从技术上讲可以，但这会造成商业间谍活动的可能。竞争对手可以通过定期下单来分析序列号增长的速度。这使他们能够追踪你的业务成果，而你可能并不希望这样。

To circumvent this, you can generate date-based + random IDs such as “20261016-32767”, and use them externally. They would be stored as an attribute of the Order anchor, but would only be used between you and the customer to refer to an order. Everywhere else in the database you would use the meaningless integer number because it is often the most convenient technically. (We’ll talk about when this may not be the case later.) Note that our system needs to validate and authenticate those external IDs even though they were generated by our system. For example, if somebody submits a request to cancel a reservation QIE3CB, we need to make sure they have the authority to do that. Maybe they just eavesdropped on somebody else’s reservation number. 为了规避这一点，你可以生成基于日期+随机数的 ID，例如“20261016-32767”，并在外部使用它们。它们将作为订单锚点的一个属性存储，但仅用于你与客户之间引用订单。在数据库的其他地方，你将使用无意义的整数，因为从技术上讲，这通常是最方便的。（我们稍后会讨论何时情况并非如此。）请注意，即使这些外部 ID 是由我们的系统生成的，我们的系统也需要对其进行验证和鉴权。例如，如果有人提交了取消预订 QIE3CB 的请求，我们需要确保他们有权这样做。也许他们只是窃听了别人的预订号码。

External IDs from outside systems / 来自外部系统的外部 ID

Some external IDs are generated by outside systems… 一些外部 ID 是由外部系统生成的……