想通过一个例子来理解 6NF [英] Would like to Understand 6NF with an Example

问题描述

我刚刚阅读了@PerformanceDBA 的论点:6NF 和 E-A-V.我很好奇.我之前一直对 6NF 持怀疑态度，因为它被描述为仅仅"在表上粘贴一些时间戳列.

I have just read @PerformanceDBA's arguments re: 6NF and E-A-V. I am intrigued. I had previously been skeptical of 6NF as it was presented as "merely" sticking some timestamp columns on tables.

我一直使用数据字典，不需要被说服使用它或生成 SQL 代码.所以我希望得到一个需要用于生成代码的字典(或目录)的答案.

I have always worked with a data dictionary and do not need to be convinced to use one, or to generate SQL code. So I expect an answer that would require a dictionary (or catalog) that is used to generate code.

所以我想知道 6NF 如何处理一个极其简单的例子.物品、描述和价格表.价格随时间变化.

So I would like to know how 6NF would deal with an extremely simple example. A table of items, descriptions and prices. The prices change over time.

那么无论如何，当转换为 6NF 时，Items 表是什么样的?什么是桌子爆炸"?发生在这里?

So anyway, what does the Items table look like when converted to 6NF? What is the "explosion of tables?" that happens here?

如果该示例不适用于这么简单的表格，请随意添加必要的内容以说明问题.

If the example does not work with a table this simple, feel free to add what is necessary to get the point across.

推荐答案

简而言之，6NF 意味着每个关系都由一个候选键加上不超过一个其他(键或非键)属性组成.举个例子，如果一个项目"由 ProductCode 标识，其他属性是描述和价格，那么 6NF 模式将包含两个关系(* 表示每个关系中的键):

In a nutshell, 6NF means that every relation consists of a candidate key plus no more than one other (key or non-key) attribute. To take up your example, if an "item" is identified by a ProductCode and the other attributes are Description and Price then a 6NF schema would consist of two relations (* denotes the key in each):

ItemDesc {ProductCode*, Description}
ItemPrice {ProductCode*, Price}

这可能是一种非常灵活的方法，因为它最大限度地减少了依赖关系.然而，这也是它的主要缺点，尤其是在 SQL 数据库中.SQL 使得强制执行许多多表约束变得困难或不可能.使用上述模式，在大多数情况下，不可能强制执行每个产品必须始终具有描述和价格的业务规则.同样，您可能无法强制某些应该应用的复合键(因为它们的属性可能会被拆分到多个表中).

This is potentially a very flexible approach because it minimises the dependencies. That's also its main disadvantage however, especially in a SQL database. SQL makes it hard or impossible to enforce many multi-table constraints. Using the above schema, in most cases it will not be possible to enforce a business rule that every product must always have a description AND a price. Similarly, you may not be able to enforce some compound keys that ought to apply (because their attributes could be split over multiple tables).

因此，在考虑 6NF 时，您必须权衡哪些依赖项和完整性规则对您很重要.在许多情况下，您可能会发现坚持 5NF 并且仅进行标准化更实用和有用.

So in considering 6NF you have to weigh up which dependencies and integrity rules are important to you. In many cases you may find it more practical and useful to stick to 5NF and normalize no further than that.

这篇关于想通过一个例子来理解 6NF的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！