构建数据库表的适当方法？ （空列与多个表）
假设我们有一个名为 Widget 的对象，我们可以为其构造一个数据库表。
现在，假设我们有两组额外的细节来描述小部件。每组数据都可以在不同的时间获得。因此，假设我们的小部件的生命周期分为三个阶段...
在第 1 阶段中，我们只有一个带有名称和描述的小部件。
widgets
-------
id (PK)
name
description
在第二阶段中，我们的小部件获得了高度和重量。
widgets
-------
id (PK)
name
description
height
weight
在第 3 阶段，我们的小部件获得目的地和运输成本。
widgets
-------
id (PK)
name
description
height
weight
destination
shipping_cost
上述架构（针对“第 3 阶段”）意味着第 1 或第 2 阶段小部件的数据库记录将具有空值。
或者，我们可以构造一个永远不会有空值的模式（但父记录可能有零个、一个或两个子记录，具体取决于小部件生命周期的当前阶段）：
widgets
-------
id (PK)
name
description
widget_specs
-------
id (PK)
widget_id (FK)
height
weight
widget_delivery
-------
id (PK)
widget_id (FK)
destination
shipping_cost
这些替代方案之一是否始终正确？每个都有合理的优点和缺点吗？如果答案取决于更多变量，那么它们是什么？在什么条件下，一种替代方案会成为明显的首选？
接受的答案将引用该主题的现代权威来源。
编辑：我觉得这很容易引起争议，但它也是一个应该有合理优点和优点的话题。缺点，因此是权威的答案。这个问题只是一个困扰我的问题，因为我已经看到它以两种方式完成，而没有理由或考虑替代方案。我只是想知道根据当前引领趋势的 DBA 类型，哪一个正确。
Let's say we have a object called a Widget, for which we can construct a database table.
Now, let's say we have two sets of additional detail to describe widgets. Each set of data is available at a separate time. So, let's say our widgets have three phases to their life-cycle...
In phase 1, we simply have a widget with a name and a description.
widgets
-------
id (PK)
name
description
In phase 2, our widget gains a height and weight.
widgets
-------
id (PK)
name
description
height
weight
In phase 3, our widget gains a destination and shipping cost.
widgets
-------
id (PK)
name
description
height
weight
destination
shipping_cost
The above schema (for "phase 3") means that the database record for a widget in phase 1 or 2 will have null values.
Alternatively, we could construct a schema that will never have null values (but instead the parent record may have zero, one or two child records depending on the current phase of the widget's lifecycle):
widgets
-------
id (PK)
name
description
widget_specs
-------
id (PK)
widget_id (FK)
height
weight
widget_delivery
-------
id (PK)
widget_id (FK)
destination
shipping_cost
Is one of these alternatives always correct? Are there justifiable pros and cons to each? If the answer depends on more variables, what are they? Under what conditions would one alternative become the obvious preferred choice?
The accepted answer will cite a modern, authoritative source on the topic.
Edit: I feel this could easily be argumentative, but it's also a topic that should have justifiable pros & cons, and therefore an authoritative answer. The question is simply one that has bugged me, because I've seen it done both ways without justification or consideration for the alternative. I'd simply like to know which is correct, according to the current trend-setting DBA types.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
范式 (BCNF / 5NF) 通常是数据库设计最可靠的基础，除非您找到令人信服的理由来偏离它。这意味着没有空值的模式应该是首选。标准化减少了冗余数据和出现异常的可能性，并最大限度地减少了设计中内置的“偏差”，使其更易于维护和扩展。
空值使数据库上的大多数操作变得复杂，并导致某些查询结果不正确。仅在您发现这样做的特殊原因时将空值添加到设计中 - 通常这些原因与 DBMS 限制有关，这些限制不允许您在不使用空值的情况下轻松实现某些约束或其他逻辑。另请记住，每当数据库设计者将空值添加到数据库中时，应用程序设计者通常都必须做额外的工作来删除或隐藏它们，以便最终用户受益。
您可以在 Fabian Pascal 的书“数据库管理中的实际问题”、Chris Date 的书中以及 EFCodd、Witold Lipski 等人的论文中找到有关空值和其他与缺失数据有关的问题的广泛讨论。
Normal Form (BCNF / 5NF) is generally the most sound basis for database design unless you find compelling reasons to deviate from it. That means the schema without nulls should be preferred. Normalization reduces redundant data and the potential for anomalies to arise and minimises inbuilt "bias" in the design, making it easier to maintain and extend.
Nulls complicate most operations on the database and cause incorrect results with some queries. Only add nulls to a design where you find some special reason for doing so - typically those reasons are to do with DBMS limitations that won't allow you easily to implement some constraint or other logic without using nulls. Also bear in mind that whenever a database designer adds nulls into the database, an application designer typically has to do extra work to remove or hide them for the benefit of the end user.
You can find extensive discussions on nulls and other issues to do with missing data in Fabian Pascal's book "Practical Issues in Database Management" and in the books of Chris Date and papers by E.F.Codd, Witold Lipski and many others.
减轻空列的选择是创建一对一的关系，或者一个小部件可以有多个权重和权重。交货规格？
这也意味着您必须 LEFT JOIN 到两个支持表来检查信息，其中单个表不需要任何特殊内容（除了在某些情况下进行 IS/IS NOT NULL 检查）。
一对一关系是一种性能优化，但这不是您问这个问题的原因......
Your option to alleviate null columns is creating one-to-one relationships, or could a widget have more than one weight & delivery specs?
It also means you'll have to LEFT JOIN to both supporting tables to check info, where a single table doesn't need anything special (other than an IS/IS NOT NULL check in certain situations).
One-to-one relationships are a performance optimization, but that's not why you're asking this question...