5NF and Database Design | 海外技术热榜
原文链接
翻译说明
本文翻译自Hacker News最新热门技术文章，内容仅供学习参考，版权归原作者所有。
完整翻译内容
数据库设计书籍文章 5NF 和数据库设计，2026 年 4 月 11 日 作者：Alexey Makhotkin steamette@gmail.com，（约 3900 字）本出版物的目标之一是解构关系数据库中基本主题的传统教学方式。之前我们讨论了第四范式（4NF）：“从历史上看，4NF 的解释是不必要的混乱”。 让我们讨论一下终极野兽：第五范式（5NF）。通常它的呈现比 4NF 更令人困惑，我们可以证明这种呈现是不必要的，而且这种混乱完全是人为的。 以下是这篇文章的路线图： - 拥有良好的、动机良好的示例至关重要。我们首先对呈现 5NF 的各种文本中使用的示例进行调查。 - 维基百科应该对某个概念有一个很好的基线解释，但就 5NF 而言，却没有。我们以维基百科的例子来讨论这些问题。 - 我们讨论更符合逻辑的表设计顺序：a）从业务需求开始，b）创建逻辑模型，c）设计物理表模式。 - 我们讨论“冰淇淋”的例子，以及这里自然出现的AB-BC-AC三角形图案。 - 我们讨论“音乐家”示例，以及此处出现的 ABC+D 星形模式。对于某些业务需求，还可以选择使用复合主键或合成主键。 - 最后，我们得出的结论是，您实际上并不需要涉及 5NF 来设计您的表模式。您从逻辑模型开始，并应用保留规范化的教科书表设计策略。 - 在另外两章中，我们讨论扩展这两个示例，并有两个不同的教学点。 目录 - 来源 - 理解维基百科示例 - 从逻辑模型开始 - AB-BC-AC 三角形：“冰淇淋”示例 - ABC+D 星形图案：“音乐家”示例 - 物理表设计：主键 - 额外章节：具体的冰淇淋偏好 - 额外章节：技能问题- 附加阅读 - 结论 在此订阅以接收更新。 ------------------------------------------------------------------------------------ 来源 首先，我们需要列出常用来源中使用的教学场景。 维基百科：推销员/品牌/产品类型，具有以下关系： - 推销员销售产品类型（例如“吸尘器”）； - 推销员销售品牌（例如“飞利浦”）； - 品牌提供的产品类型； en.wikipedia.org/w/index.php… Decomplexify：冰淇淋品牌/口味/朋友，具有以下关系： - 品牌提供口味（例如，Frosty's 提供香草、巧克力、草莓和薄荷）； - 朋友喜欢口味（例如杰森喜欢香草和巧克力）； - 朋友喜欢品牌（例如 Jason 喜欢 Frosty’s 和 Alpine）； www.youtube.com/watch?v=GFQ… 巴里·约翰逊 (Barry Johnson) 的数据揭秘博客：音乐会/音乐家/乐器/表演。 - 音乐家参加音乐会； - 音乐家能够演奏乐器； - 音乐家在某场音乐会上演奏乐器； datademythed.com/posts/5nf_m… 的文本还讨论了传统上对 5NF 的不良治疗。当时我们俩在评论里进行了讨论，让我在理解上有了一些进步。在这里我将讨论我对这个业务案例的处理，因为它在结构上似乎与前两个示例不同。 理解维基百科示例 维基百科以迂回的方式呈现其示例。首先，它显示一个三列表（“旅行推销员”、“品牌”、“产品类型”）。 [维基百科示例：旅行推销员按品牌列出的产品可用性] 此表是什么意思？我们稍后再讨论。 然后是下面的引用：“在没有任何规则限制 v尽管旅行推销员、品牌和产品类型的可能组合，上面的三属性表对于正确建模情况是必要的。 然而，假设以下规则适用：旅行推销员的库存中拥有某些品牌和某些产品类型。如果品牌 B1 和品牌 B2 在他们的保留范围内，并且产品类型 P 在他们的保留范围内，那么（假设品牌 B1 和品牌 B2 都生产产品类型 P），旅行推销员必须提供两个品牌的产品类型 P；也就是说，销售人员不能只销售 B1 的产品 P 或只销售 B2 的产品 P。在这种情况下，可以将表分成三部分： [维基百科示例：旅行推销员的产品类型/旅行推销员的品牌/品牌的产品类型] 在这种情况下，Louis Ferguson 不可能拒绝提供 Acme 生产的吸尘器（假设 Acme 生产吸尘器），如果他销售 Acme 生产的任何其他产品（熔岩灯）并且他还销售吸尘器任何其他品牌（Robusto）生产的清洁剂。” “无法拒绝”的措辞，以及“必须提供两个品牌的 P 型产品”的整个前提描述了一个奇怪的场景。我不确定现实生活中是否发生过这种情况。 抛开表格：规则本身很奇怪，这意味着我们无法处理正常情况。如果我想开始销售 Robusto 真空吸尘器，那么我还必须开始销售 Acme 真空吸尘器。如果我想销售 Acme 面包盒，我还必须销售 Acme 吸尘器。 这没有商业意义，因此您无法从此示例中理解 5NF。如今，我可能明白这种人为的措辞在教学上试图实现什么目标，但我认为它在这方面失败了。它对于理解数据库设计没有用。 从逻辑模型开始 整个前提是不合逻辑的。呈现出一张表格，我们问：这张表格的含义是什么？然后我们把这个表拆成三个表，我们再问：c是什么那些表的含义可能是什么？我们问：如果有一条毫无意义的额外规则怎么办？如果我们考虑到它，我们会对表格做出不同的解释吗？ 相反，我们必须从与实际业务场景对应的逻辑模型开始。当逻辑模型完成后，我们可以使用教科书表设计策略构建物理模式。结果将完全标准化：没有冗余，也没有异常。 围绕 5NF 出现了两种逻辑设计模式： - AB-BC-AC 三角形（参见下面的“冰淇淋”示例），以及 - ABC+D 星形图案（参见“音乐家”示例）。 AB-BC-AC 三角形：“冰淇淋”示例 这是我对“冰淇淋”示例的重新表述，在关于数据库规范化的 Decomplexify 视频中介绍：“市场上有多个冰淇淋品牌，例如“Frosty’s”、“Alpine”、“Ice Queen”等。每个品牌都生产一种或多种口味的冰淇淋，例如香草、草莓和朗姆酒葡萄干。我们想创建一个关于我们朋友的冰淇淋的数据库每个朋友都喜欢一些品牌，也喜欢一些口味。也就是说，如果一个朋友喜欢品牌 A 和 B 以及口味 1 和 2，则意味着他们恰好喜欢 A-1、A-2、B-1 和 B-2（仅限于该品牌实际生产的配对）。” 有两点需要注意：第一，这个例子比维基百科的例子更合理，我们就采用它。其次，没有要求处理特定的口味。例如，我们不会记录上述朋友另外喜欢 D-4 的事实，也不会记录他们不喜欢 B-1 的事实。 让我们使用《数据库设计手册》中的符号写下与上述描述相对应的逻辑模型。我们将拥有三个锚点（实体）： 锚点 ID 示例 表名称 --------- ------------ ------------ 品牌“Alpine”品牌 风味“香草”口味 朋友“Jason”朋友 在真实的数据库中，我们会使用 prper 整数 ID，但这是一个小型教学数据库，因此使用所谓的自然键（即唯一字符串）是有意义的。我们假设您所有的朋友都有不同的名字。在教科书的表格设计策略中，每个anchor都有一个对应的表格。表名写在最后一列中。 这里我们不需要任何属性，因为有关实体的所有信息基本上都包含在 ID 中。 但最有趣的部分是链接列表。 5NF 主要是关于链接。 --------------------------------------------------------------------------------------------------------- Anchor1 : Anchor2 Cardi- Sentences 表或列名称 nality ---------------------------------- ----------------- ---------------------------------------------------------- -------------------------- Brand : Flavor M:N 一个品牌生产多种口味brand_flavours 一种口味由多个品牌生产 Friend : Brand M:N 一个朋友喜欢几个品牌friend_brands 一个品牌被几个朋友首选 Friend : Flavor M:N 一个朋友喜欢几种风味friend_flavors 一种风味受到多个朋友的偏爱------------------------------------------------------------------------------------------------------------ 链接是两个锚点之间的关系。这里我们有三个链接，将三个锚点连接成一个三角形，正如我们提到的：[品牌/风味/朋友锚点]所有三个链接都具有 M:N 基数。它清晰可见，因为所有句子都包含“几个”一词。在教科书的表设计策略中，每个M:N链接都有一个对应的两列表（也称为联结表）。的名称表格写在最后一列中。 这是根据逻辑模型创建的三个链接表。它使用与 Decomplexify 视频中提供的相同的数据集。 [数据集：brand_flavors、friend_brands、friend_flavors] 我们在这里可以看到的一件事是 Jason 喜欢巧克力，但没有一个品牌生产巧克力。 对于感兴趣的读者来说，一个有用的练习是编写一个 SQL 查询，根据每个朋友的喜好返回适合他们的品牌产品。 请注意，还会存在另外三个表：品牌、风味和朋友，其中包含链接表中使用的 ID。 [数据集：品牌、口味、朋友] 请注意，对于每个实体，还有一行不存在于任何链接关系中。我们知道 Coldflash 品牌，但我们不知道它提供哪些口味，也没有人喜欢它。我们有一种奇异果口味，不是任何品牌生产的，也不是任何人喜欢的。我们还有苏西，我们还没有登记她的偏好。 ABC+D 星形模式：“音乐家”示例 现在让我们讨论 Barry Johnson 的“5NF：缺失用例”帖子中的示例。 假设我们想要记录有关音乐会和在这些音乐会中演奏某些乐器的音乐家的信息。我们从三个锚点开始： 锚点 ID 示例 表名称 ------------------------ ------------------ ------------- 音乐会“christmas-2025”音乐会 乐器“小提琴”乐器 音乐家“Patricia”音乐家 现在让我们起草一些可能的链接。这是数据库建模示例之一，自然语言可能会误导您，因此我们需要小心和精确。 用简单的英语来说，我们会说：“一位音乐家在音乐会中演奏了一种乐器”，但这句话包括三个锚点，而不是两个。链接始终连接两个锚点。尽管关系模型允许与两个以上元素的关系，但我们坚持只使用 2 元素链接，因为它们可以帮助您设计明确的表格。 以下是现实世界音乐会的示例，列出了音乐家及其乐器： - Marc，小提琴； - 吉尔斯，小提琴； - 弗拉德，中提琴； - 约万，大提琴。 你看有几个音乐家可以演奏一种乐器。如果你想一想，一位音乐家可能会演奏多种乐器。 当我们这样做时，我们为什么要构建这个数据库？假设我们想要跟踪音乐家应该得到多少钱。小提琴演奏家的报酬为 x 美元；也许有人被要求帮忙，在前半场演奏一些铙钹，在最后演奏锣，所以他们会得到 z 的报酬。 当你看到某物的列表时，通常它就是一个锚点。列表项可以被计数，并且您可以向列表中添加一项：经典锚点。 我们需要为此找到一个词 - 让我们尝试“性能”： 锚点 ID 示例 表名 ------------- -------------------------------------------------- -------------- 性能 <可能有两个选项，见下文> 性能 找到这样的词通常很困难，因为自然语言是模糊的。 （请注意，我们使用合成整数 ID 进行表演。）现在让我们尝试查找四个锚点之间的链接。音乐会是由个人表演组成的。表演涉及特定的音乐家演奏特定的乐器。让我们将其形式化： -------------------------------------------------------------------------------------------------------------------------- Anchor1 : Anchor2 Cardi- Sentences 表或列名称 nality -------------------------------------- ----------------- --------------------------------------------------------------------------------- Concert : Performance 1:N 音乐会由多个 Performance 表演组成。 表演者mance 仅是一场音乐会的一部分 Concert_id 乐器：演奏 1:N 乐器在多个演奏中演奏。 一个演奏仅涉及一个乐器instrument_id 音乐家：演奏1:N 一个音乐家可以演奏多个演奏。 一场表演仅由一位音乐家musician_id 进行 -------------------------------------------------------------------------------------------------------------------------- 阅读句子：它们可能听起来有点尴尬，但您需要确认它们是否有意义。确保正确使用“只有一个”和“几个”至关重要，因为这就是定义基数的方式。如果需要的话大声朗读，或者读给其他人听——这就是防止设计错误的方法。 在这种情况下，所有三个链接的基数都是 1:N。 这是一个具有独特三角星形的图表：[音乐会、音乐家、乐器、表演：锚点和链接] 这就是为什么我们将其称为 ABC+D 星形图案。我们有三个“简单”的词：音乐会、音乐家和乐器。但您还必须认识到，性能有一个基本概念。使用形式化的方法，例如“数据库设计手册”中介绍的方法，有很大帮助，使设计过程更加可靠和防错。 物理表设计：主键 范式的概念只有在我们谈论物理表设计时才会出现。冰淇淋的例子简单明了，但当前的场景有点复杂，因为我们必须讨论唯一性约束。 教科书表格设计策略可以直接应用： - 性能锚被实现为“性能”表； - 所有三个链接都是 1:N，因此它们被实现为该表中的列（“concert_id”、“musician_id”和“instrument_id”）。 但这里是问题：该表的主键是什么？在大多数锚表中，我们会使用合成 ID 列，但在这里我们可以注意到（concert_id、musician_id、instrument_id）的组合必须是唯一的。用尴尬的半正式英语来说：音乐家在一场特定的音乐会上仅演奏一次乐器即可获得报酬（这是根据业务要求得出的）。 如果我们使用合成 ID 列，则需要添加唯一性约束： CREATE TABLE Performances ( id INTEGER NOT NULL PRIMARY KEY, Concert_id INTEGER NOT NULL,musician_id INTEGER NOT NULL, Instrument_id INTEGER NOT NULL, UNIQUE (concert_id,音乐家_id,instrument_id) ); 数据看起来是这样的： [performances 表，使用合成 ID 主键] 但关系理论教科书想让你知道的是，你也可以使用复合主键（也作为唯一性约束）来设计这个表： CREATE TABLE Performances ( Concert_id INTEGER NOT NULL,音乐家_id INTEGER NOT NULL, Instrument_id INTEGER NOT NULL, PRIMARY KEY (concert_id,音乐家_id,instrument_id) ); 数据将如下所示： [性能表，使用复合主键] 复合主键与合成主键的选择仅在业务需求假设唯一性时出现。在其他情况下，没有唯一性，必须使用合成主键。 想象一下在线游戏，用户可以购买某些物品并将其作为礼物送给其他用户。同一用户可以多次向同一好友赠送同一类型的物品，因此不存在唯一性。不过，我们仍然有相同的 ABC+D 星形图案。 让我们回到三列表的变体。它与维基百科和许多其他教科书和教程中使用的结构相同。我们不需要将其拆分为三个两列表，因为业务需求与“冰淇淋”示例不同。 摩尔重要的是，我们根本不需要根据 5NF 分解进行推理。我们只需要定义业务需求，并以简单的方式构建物理表。 然而，我们已经看到了典型 5NF 教程中讨论的两种类型的表： - 三个两列表（AB-BC-AC 三角形）； - 一张表（ABC+D 星）： - 如果需要唯一性并且我们想要一个复合 PK，则为三列； - 如果没有唯一性或者我们想要合成 PK，则需要一个额外的 ID； 而且，我们不需要执行这种不合逻辑的“拆表”操作，因为我们从一开始就正确设计了所有的表。实际上，它们不会作为数据库设计过程的一部分进行拆分或连接。 附加章节：特定的冰淇淋偏好 两种呈现的模式并不相互排斥。您可以有多个不同的链接，以不同的方式连接相同的锚点。 让我们回到冰淇淋的例子并扩展它。我们的一些朋友特别挑剔：他们喜欢特定品牌的特定口味，例如 Coldflash 的猕猴桃，但不喜欢 Coldflash 的其他口味，也不喜欢其他猕猴桃品牌。 我们需要通过扩展上面定义的逻辑模型来处理这个例子。值得注意的是，原始行为保持原样：所有逻辑模式和现有表保持原样。我们只向逻辑模式添加新条目，并且添加一些新表。 讨论了“音乐家”的例子后，我们可以立即认识到如何建模“FranklovesColdflashkiwi”。我们引入了一个名为“偏好”的新锚点，以及排列为 ABC+D 星形图案的三个链接。 逻辑模式基本上是“音乐家”示例的复制粘贴。 Anchors: 锚点 ID 示例 表名称 ------------------------ ---------------------------------------- ------------- 首选项 <可以有两个选项> 首选项和链接： --------------------------------------------------------------------------------------------------------------- Anchor1 : Anchor2 Cardi- Sentences 表或列名称性质 ---------------------------------- ----------------- ---------------------------------------------- -------------------------- Friend : Preference 1:N Friend 可以有多个 Preferences 偏好。 一个偏好仅属于一个朋友friend_id 品牌：偏好1：N 一个品牌涉及多个偏好偏好。 一个Preference仅指一个Brand Brand_id Flavor : Preference 1:N 一个Flavor涉及多个Preferences偏好。 一个 Preference 仅指一种 Flavorflavor_id ---------------------------------------------------------------------------------------------------------------------------- 与“音乐家”示例相同，我们可以选择合成 ID 或复合 PK。在此示例中，每个（朋友、品牌、风味）组合必须是唯一的，因此如果需要，我们可以使用复合 PK。 根据这些新元素，我们创建一个新的“偏好”表，该表与其他三个表一起存在：“brand_flavors”、“friend_brands”和“friend_flavors”。我们仍然使用相应设计的两组表格来捕获有关更广泛偏好和特定偏好的信息。 本节的重点是强调两种模式同样有用，一种模式不能替代另一种模式。有时它们甚至可以同时使用。其中哪一个适用仅取决于您的业务需求。 附章：技能问题我们回到“音乐家”的例子。如果您阅读“5NF：缺失的用例”帖子，您会在某个时候看到一个想法f 引入“技能”。基本上它是连接音乐家和乐器的链接。这个想法在自然语言中是有意义的：“爱丽丝会拉小提琴，所以她被邀请参加古典音乐会”。 数据通过文本中的技能建模，如下所示：[音乐会/音乐家/乐器/表演，包括技能]这与本文中讨论的模型不同。为什么？我对这个问题思考了很长一段时间，并开始从不同的角度来看待这个问题。 我认为“音乐家可以演奏乐器”是一个有效的链接，但它对应的是不同的业务流程。我们讨论了捕获有关谁在特定音乐会中演奏哪种乐器的信息。 但如果你想一想，你可以在音乐会上演奏一种乐器，而不必声明你有演奏它的技能。 考虑一下该帖子中的引文：“例如，进一步观察我们的示例，很容易想到 [Skill] 中的 SkillRating。这可用于帮助决定哪个 [Musician] 实例以及相应的 [Instrument] 实例将被邀请成为 [Concert] 实例的一部分。” 通过捕获有关哪些音乐家愿意被邀请参加音乐会演奏某些乐器的信息来扩展我们原始的逻辑模型是完全有意义的。链接如下： ---------------------------------------------------------------------------------------------------------- Anchor1 : Anchor2 Cardi- Sentences 表或列名称 nality ---------------------------------- ----------------- -------------------------------------------------- -------------------------- 乐器 : 音乐家 M:N 乐器可以由多个音乐家演奏 Musician_skills 音乐家可以演奏多种乐器 ----------------------------------------------------------------------------------------------------------------- 请注意，此链接具有 M:N 基数。因此，我们必须添加另一个两列表（也称为连接表）。它在两个方面都完全独立于“性能”表。 首先，我们可以记录爱丽丝可以拉小提琴，但我们没有任何关于她演奏任何东西的记录。这是正常的：也许她刚刚开始，或者我们自己的数据集还远未完成。 其次，有人可以在音乐会上演奏，但他们不一定想被邀请参加更多的音乐会。也许他们已经退休了，只是在特殊场合例外。 或者，需要有人在马勒第六交响曲中演奏锤子（全曲只有两到三击）。可能不需要为此拥有“技能”，但你肯定希望在阵容中被提及，因为它是非常重要的部分。 所以，这是一个你想要实现什么的问题。这是音乐家的一种市场吗？那么你需要“musician_skills”。或者，如果您想记录谁在何时演奏 - 这就是“表演”表的用途。也许你两者都想要。由您决定。 附加阅读 如果您想更多地了解范式的历史，这里还有另外三篇文章可能会很有用。 1. William Kent “关系数据库理论中五种范式的简单指南” (1982) 5NF 在第 4.2 章中讨论。我们已经使用本文来更好地理解 4NF 定义的历史方面。其中有一个令人困惑的句子：“粗略地说，当记录类型的信息内容无法从几个较小的记录类型（即每个记录类型的字段少于原始记录）重建时，我们可以说该记录类型处于第五范式。” 看来这里的负面因素太多了，很难理解。但我不会尝试编辑这个句子：我的目标是提出一种完全不同的方式来达到相同的结果。 2. Andrei Pall“数据库规范化”（2020）这篇文章包含一个漂亮的表格，显示了完整的范式形式（包括非数字形式），解释了每个规范形式的具体标准。 3. Hugh Darwen《关系数据库理论简介》（2010） 该书可免费下载。在第 11 页上，您可以找到“给教师的注释”，仅此一项就具有很大的启发性。它说4NF只是5NF的一个简单特例，这是一个有用的精神指导。 另一句话是：“同样在第 7 章中，[我的目标是]以与通常呈现的顺序相反的顺序来研究范式。我把 6NF 放在第一位，因为它是最简单的，也是最极端的。对我来说更重要的是处理 5NF 并在 BCNF 和函数依赖之前加入依赖关系（尽管我确实把那些满足 BCNF 但不满足 5NF 的病理情况留到最后讨论）。” 这有力地证明了我自己教授范式的方法是合理的——我也认为 6NF 是基本形式。 5NF 在第 7.4 节（第 185 页）中以“亨利八世的妻子”为例进行了介绍。这不是一个很好的例子，因为它与典型的现实场景相当不相符。尽管如此，这本书以及达尔文的其他材料都是一笔财富。 ------------------------------------------------------------------------ “数据库设计书籍”(2025) [] 了解如何从业务需求到数据库模式 如果这篇文章有用，您可能会发现这本书也很有用。 目录和样本章节 本书长度：145 页，约 32.000 字。提供 PDF 和 EPUB 格式。 购买价格为 32 欧元------------------------------------------------------------------------ 结论 传统上 5NF 是用一个 3 列表的无动机示例来呈现的。然后这个表被“分割”成 three 2-column tables.分裂操作的动机不是很好，并且在实践中不会发生。 这使得 5NF 更难理解，并助长了更高范式周围的神秘主义气氛。 我们提出了一种更直接的方法，更好地符合数据库设计实践。我们从业务需求和逻辑架构开始。基于此，物理桌子设计的两种模式自然而然地遵循：AB-BC-AC三角形，或ABC+D星形模式。当以这种方式设计时，这两种模式都是完全标准化的：没有异常，也没有冗余。 我很高兴听到您的反馈：Alexey Makhotkin seasonette@gmail.com。 © 数据库设计书 2026
翻译声明：本文由AI自动翻译，如有不准确之处欢迎指正
🙏 如果本文对你有帮助，欢迎打赏支持，你的鼓励是我持续输出优质内容的最大动力！ 💴 打赏通道：点击文章末尾「赞赏」按钮即可，每一分支持都是我前进的动力～
