数据库规范化设计 - 单个表格还是多个表格

Question

这应该在数据库中表示为1个表还是3个表？我和我的朋友对此有不同的看法，所以我想了解一下大家的看法。（也许应该投票选择哪种解决方案？）

Create Table Order
// Basic fields of the table
 - ID (Primary key)
 - CustomerID  (integer, with a FK)
 - Quantity
 - ProductID  (integer, with a FK)

 // Then depending on user selection, either these fields need to be specified 
 // (could be factored out to a separate table):
 {
 - InternalAccountID (integer, with a FK)
 - InternalCompanyID (integer, with a FK)
 }

 // Or these (could be factored out to a separate table):
 {
 - ExternalAccountNumber (free text string)
 - ExternalCompanyName (free text string)
 - ExtraInformation (free text string)
 }

一张表的方法：

优点：

• 性能（相对于两张表，外键检查和无需连接只需要插入一次）
• 可能占用更少的空间（额外的表有开销+索引+额外的ID字段）
• 只有一张表
• 几乎不值得为了2+3个字段（或什么？）而将其拆分成新表

缺点：

• 可为空的字段
• 可能需要额外的“类型”列（可以跳过）
• 破坏第三范式（？）

请提供优缺点以及个人意见。:)

编辑：我尝试通过使用不同的实体来简化示例，所以任何修改模型的建议都不会真正帮助我。即请关注技术方面而非领域模型。

- AtliB

---

嗯，我觉得这有点不同。 1）上面的内容有点具体（相对于一些愚蠢的100多列表格而言）。 2）使用任何继承来结构化都没有意义（因为它不是继承关系）。 3）这是一个纯粹的一对一映射。 4）“side”表中的数据永远不会链接或在其他地方使用（与“如何构建评论表”的讨论不同）。 - AtliB

这个问题与它非常相似：https://dev59.com/v0jSa4cB1Zd3GeqPJu5- - AtliB

看起来模型已经坏掉了... 订单有一个客户ID，所以所有的InternalAccountID、InternalCompanyID、ExternalAccountNumber、ExternalCompanyName、ExtraInformation，可能都是从一些客户表中复制过来的... - pascal

似乎模型已经破损了。不过，这并不是因为客户可能有多个账户。无论如何，这只是一个虚构的问题（所以列名是虚构的）。真正的问题是何时将字段留在表中或将其拆分成新表（在这种情况下，需要两个额外的表，因为这些字段是互斥的）。 - AtliB

性能在这里并不一定是优点。插入/更新操作可能会受到影响，因为必须在一个臃肿的表上重新构建索引，而不是3个紧密定义的表。这取决于数据库和表类型，直到表变得非常庞大才会产生影响，但没有任何事情是没有副作用的。 - tadamson

Answer 1

希望这是不言自明的。

- Damir Sudarevic

---

感谢提供图表！是的，这就是你可以为表格设置逻辑结构的方式（当然也包括物理方面）。但在实现这个设计时，order+internalorder+externalorder 应该是 1 张表还是 3 张表？为什么呢？原则上，从实际世界的角度来看，这些实体之间实际上并没有继承关系（至少不是真正的继承关系）。 - AtliB

这里展示的是三个表，Order表包含所有通用列，Internal和External Orders是订单的一些特殊子类型。通过重构公司和账户表以容纳内部和外部公司，您可能可以将所有内容移动到一个表中。 - Damir Sudarevic

Answer 2

我的意见是，如果

 // Then depending on user selection, either these fields need to be specified 
 // (could be factored out to a separate table):
 {
 - InternalAccountID (integer, with a FK)
 - InternalCompanyID (integer, with a FK)
 }

 // Or these (could be factored out to a separate table):
 {
 - ExternalAccountNumber (free text string)
 - ExternalCompanyName (free text string)
 - ExtraInformation (free text string)
 }

如果每个订单都有一个对应帐户ID (即，您不能有3个帐户ID)，那么将其作为一个表格。 为解决空值问题，您可以添加一个名为InternalCustomer（布尔类型）或CustomerType（varChar）的列，您可以使用它来定义内部或外部客户，以了解您应该查看哪个客户的两组字段之一。

由于我们不知道这些数据的完整用途或整个数据库的模式，因此对此的任何响应都无法得到充分的资格。

- Tommy

---

是的，这是一个纯粹的一对一关系，这些额外的数据只与订单本身有关（没有其他对象需要这种数据）。 - AtliB

Answer 3

随着数据量的增加，从两个表中选择可能比从一个表中选择更快。有时候，为了提高性能，成熟的数据库会进行这种重构（分区）。

想象一下，在多个表连接时使用它，其中一些条件在这个表中，而其他条件在不同的表中。

select from order join customer using (customer_id)
where
    order.order_date between ? and ?
    and customer.name = ?

可能会导致从磁盘获取所有日期的order行，然后丢弃其中许多行，因为它们不匹配连接。这种从磁盘获取数据的操作注定会很慢，并可能破坏您的RAM缓存。

select from order join order_detail using (order_id) join customer using (customer_id)
where
    order.order_date between ? and ?
    and customer.name = ?

在这种情况下，当它从磁盘加载所有order行时，它不会像以前那样受到太大的影响，因为表更窄更小。它不需要加载所有对过滤无关的冗长字段。最终，在连接到customer之后，它将仅获取与所有条件匹配的那些order_detail行。

如果您预计这将是大型的，则应考虑拆分表，使得最关键的搜索字段位于一个表中，而“数据”字段位于其他一对一表中。

底线是：正常形式和域是一回事，但性能通常需要权衡。您可以隐藏其中一些（用视图覆盖拆分），但不能全部（为了更快的选择而重复/聚合字段）。

- Konrad Garus

---

在我的样本中，我实际上总是筛选基础字段，而不是"副表"。因此，在2个或3个表中搜索字段可能会比1个表更昂贵。 - AtliB

对的，所以两到三个表格可能会更快。 - Konrad Garus

通常情况下，您将通过索引（比表本身要小得多）进行操作。从索引中获取“行定位器”，然后直接从单个表中获取条目。如果有更多的表，那么这将是另一个提取，从而减慢速度。我无法想象什么可能会使具有多个表格的情况更快（假设表格已正确索引）。 - AtliB

你看了我的例子吗？没错，索引可以帮助你定位行。但是如果你有更复杂的查询（使用其他字段或连接，不使用索引），它可能会从磁盘中获取比附加条件所需更多的行。在这种情况下，表越窄越好。 - Konrad Garus

当然，我说过“我实际上总是会过滤基本字段”，这些字段非常具有选择性。但是我同意，如果您按非索引列进行过滤（因此被迫扫描整个表），那么肯定会更好。 - AtliB

即使您在索引列上进行过滤，规划器也可能选择不执行索引扫描。特别是如果您同时在几个索引列上进行过滤，它可能会选择获取一个索引的所有行，然后扫描它们以获取其他条件。经常发生这种情况的一种情况是当您使用许多替代方案的“OR”或“IN”，或者使用“JOIN”时。 - Konrad Garus

Answer 4

在客户下订单之前，是否与客户关联帐户信息（即您是否有另一个表格来跟踪哪些帐户ID可以使用给定的CustomerID）？您能否将所有帐户抽象为一个合理统一的模式（其中可以有几个空值），以便您拥有一个通用的AccountId（代理键），然后Account表具有3个varchar字段和一个用于跟踪帐户类型的字段（用于计费等）？

如果您能够这样做，那么您的订单只需跟踪一个AccountId，因为订单（作为实体）实际上并不关心使用了哪种付款方式 - 它只关心该用户的帐户ID是否合法/存在/已批准。其他所有事情都是别人的事情（例如计费或检查资金等），而该实体及其处理将需要更多数据。

这使得您的订单保持干净且无空值，并促进了责任分离。

从概念上讲，您的订单实际上是所谓的事实表 - 仅携带数字和FK，项目大小小但数量巨大。

所以：

 Table Order (
     - OrderId
     - Quantity
     - ProductId
     - DiscountId -- sonner or latter :-)
     - AccountId
     - PaymentStatus -- probaly FK as well or predefined constant
 )

 Table Account (
     - AccountId
     - BillingInfo  -- akka ext acct number as text
     - PrincialName -- akka ext company name, some equivalent for internal acct-s
     - AdditionalData
 )

- ZXX

Answer 5

如果你想避免数据重复，你应该选择一个2或3个表的解决方案。例如，如果在订单表中有“External”列，值可能会出现多次。如果数据看起来像这样：

ID   ExternalCompanyName
1    ACME
2    ACME
3    My Company
4    ACME

现在，如果ACME更名为ACME, Inc.，则必须更新许多行。如果表已经规范化，外部公司在单独的表中，您只需要更新一行。请注意，可能有一个关于将账号编号放在自己的表中的讨论，但我们将其留给极端规范化。

似乎订单和公司/账户之间没有1对1的关系，除非每个公司/账户只能有一个订单。它听起来更像是1对多的关系。

现在，如果在单表环境中更新ExternalCompanyName时出现错误，并且只更新了一些行。您就会得到一种坏数据的情况，一些行带有ACME，一些行带有ACME, Inc.

此外，如果这确实是1对多的关系，那么您并没有节省空间。您正在重复订单中的数据，而不是将其存储在另一个表中。

- bobs

---

谢谢，但是 External** 列的目的实际上是拥有不应更改的即席信息。如果我想要重复使用一些数据，我会设置一个内部账户并使用那些数据。 - AtliB

Answer 6

我绝对不会选择三表解决方案。将这些数据分成三个表，你就不能有任何查询返回完整的订单头，而不连接外键，每次插入新订单都会更新多个表和索引，这对并发是一个问题。我建议使用两个表，一个用于内部订单，另一个用于外部订单。对于那些需要从两组订单中查询数据的情况，定义一个视图，该视图是两个表的联合。

我很惊讶看到产品ID和数量作为订单头的一部分。我见过的每个订单跟踪数据库都将订单项拆分为单独的表，使用订单ID作为外键，以便单个订单可以包括多个产品（或具有不同数量、交货时间等的相同产品）。

- PaulMcG

---

关于您最后一个观点：我尝试简化示例，使用不同的实体而不是我实际使用的实体，因此对于修改模型的任何建议都无法帮助我。 - AtliB

在同时更新多个表时，不存在并发问题，因为有数据库事务存在，请不要忘记它们。 - Illarion Kovalchuk

@Shaman - 是的，我熟悉事务的概念。并发问题在于，在我的事务期间，我可能会阻塞其他无辜的旁观者，他们并不真正妨碍我，但由于我正在更新一些我们恰好共享的索引块，所以被耽搁了。我在事务中更新的表和索引越多，我就越有可能挡住别人的路。这就是我所说的“并发问题”。 - PaulMcG

Answer 7

我不是一个纯粹主义者，所以当3NF有意义时它是好的...但你不必认为它总是有意义的。

从实用的角度来看，你的目标是什么？你的优缺点列表是一个很好的开始。我会根据需要添加一些更多的想法到这个列表中。

1）你的数据库中是否有任何其他表需要与这些数据相关联（例如连接）？这是关系型数据库的重点。

2）你的数据库会增长吗？即使现在只有一个表是有意义的，将来是否仍然有意义？如果你发现自己想要添加更多的表，而你的非规范化表强制你“绕过”它，处理额外返回的行、执行时间变慢等问题，你会后悔的。

3）当你的客户获得一个新的外部账户时，或者其他情况，会发生什么？你会创建一个全新的记录吗？你将如何回答类似“某某客户的账号是什么”的问题。

...

我认为通常情况下，我会选择可扩展性，这在这种情况下可能意味着3NF。一个表在非常狭窄的范围内更容易处理，但如果有任何变化，你将需要处理“如何将该表拆分成正确相关的3NF表，而不会破坏已创建在它上面的所有依赖关系？”那真是一场噩梦。

- dave

---

1. 不需要，没有其他表需要与这个表/这些表相关联。
2. 是的，但不会有显著影响（也许几年后会增加一百万行）。
3. 外部账户被视为“临时”账户，因此它们永远不需要再次使用。如果您想要一个永久的账户，您可以设置一个内部账户。

- AtliB

好的 - 如果我理解到目前为止所读到的内容（在这里和其他答案中），你需要4个表 - 原因如下：1）每个人都需要客户ID（内部和外部），以便与订单相关联。因此需要一个“客户”表。2）由于目的是跟踪订单，因此需要一个“订单”表。3）假定您的“内部”客户将来会重复使用他们的帐户进行未来的订单，因此当他们登录或者无论您如何设置时，您需要经常搜索该表。因此，“内部账户”表非常重要。这就留下了“外部”... - dave

...账户。我有一些问题，想知道为什么您需要费心创建外部账户号码。（它们将用于什么——将来会搜索它们吗？）外部公司名称可以放在客户表中，以及额外的信息。无论如何，我会将外部客户信息完全放在客户表中，并且只是隐含地“知道”它们是外部客户，因为它们没有与内部账户相关联。如果您必须为您的外部客户拥有“账户”信息，则“外部账户”表将减轻对其他表的搜索负担。 - dave