一张表还是多张表？（涉及IT技术）

Question

我正在尝试设计一个应用程序来存储学术参考信息。问题是每种不同类型的参考文献（例如期刊文章、书籍、报纸文章等）都需要不同的信息。例如，期刊参考文献需要期刊标题和文章标题，以及页码，而书籍需要出版商和出版日期，期刊文章则不需要。

因此，我应该在我的数据库中将所有参考文献存储在一个表中，只有在不适用时才留下空白字段，还是应该有各种表，如BookReferences、JournalReferences、NewspaperReferences，并将适当的参考文献放入每个表中。然后问题就是这将使搜索所有参考文献变得更加困难，而且编辑可能要分别进行。

（顺便说一下，我计划使用Ruby on Rails进行这个项目，但我怀疑这对这个设计问题没有任何影响）

更新：

对此还有什么看法吗？我希望得到一个简单的答案，说某种方法肯定被认为是“最好的”——但通常情况并不像这么简单。Single-Table Inheritance选项看起来很有趣，但我很难找到太多关于它的信息——我可能会在这个网站上发布另一个问题。

我在Olvak's answer和Corey's answer之间犹豫不决。Corey的答案给出了一个好理由，说明Olvak的答案不是最好的，但Olvak的答案给出了好理由，说明Corey的答案不是最好的！我从未意识到这可能会如此困难...

任何进一步的建议都将不胜感激！

- robintw

---

我真的很喜欢这个问题，谢谢。我一直在思考一个电子商务设置中的产品表的类似问题，这里的答案可以轻松应用于那个问题。干杯。 - jammus

只是好奇：你预计会有多少条记录？显然只是一个大致的数字。我认为这也应该是最终决定的一个因素。 - nickf

如果你对Olvak和Corey的回答感到困惑，可以看一下投票情况。作为一个从事数据库工作15年的人，我更倾向于Olvak的解决方案。投票结果似乎也表明这是正确的方法。 - Tom H

@Tom H：很好的观点。我肯定倾向于他的解决方案。 - robintw

@nickf：好问题——问题是我真的不知道。里面肯定会有数百个不同的引用（只是为了支持我的个人使用），但我计划将其作为公共Web应用程序，所以它可能会疯狂地起飞（我们总是可以希望！） - robintw

Answer 1

我会选择为所有引用使用单个表，但对于不适用于所有引用类型的元数据，例如BookReferences等，需要额外的表。

搜索和查询并不会更难 - 毕竟，您可以创建一个视图，像单表解决方案中一样汇总所有信息，然后进一步查询该视图。

将所有内容放在一个带有许多null的表中可能看起来是更简单的解决方案，但实际上它会导致很多问题。例如：使用单独的表可以定义每个BookReference所需的字段，但如果所有内容都在一个表中，则每个字段都必须可为空，因此是可选的。这也会更容易插入无效数据，例如同时包含非null期刊名称的书籍参考。

编辑：有些人似乎害怕连接操作。不要害怕连接！如果您在几个查询中使用完全相同的连接，那确实很麻烦，但在这种情况下，连接应定义在视图中，并且您的查询应查询该视图。视图实际上是关系数据库中的基本抽象，您应该出于与在代码中使用函数相同的原因使用它们：以避免重复，并封装和创建抽象。

编辑：有一些关于性能的评论。预测DB模式的性能非常困难，因为它经常是非直观的。例如，几个表之间的连接很容易比单个表的完全表扫描更快 - 这完全取决于查询类型，数据性质，可用索引等等。此外，在许多数据库系统中，您可以使用诸如物化视图之类的功能来针对不同的查询优化性能，而不会损害逻辑模型。在我看来，“为性能而去规范化”这一做法大多是流行迷信，除非您是Google或Flickr。

- olavk

---

你抢先说了我的话，小偷！ :) - Vinko Vrsalovic

文档管理方法（例如 Documentum 使用的方法） - Manrico Corazzi

我该如何链接到其他表格？例如，如果我的参考表中的记录1是一本书的参考文献，因此链接到BookReference表中的一条记录，那么我怎么知道要在那里查找它，而不是在JournalReference表中？ - robintw

两个选项：1）检查所有其他表格，看看哪些（如果有的话）与参考编号#1相关联。2）如果参考只能是一种类型，您可以添加一个字段，指示每个记录的类型。 - Dave Sherohman

我同意这个设计。BookReferences 将包括一个指向父 References 表的外键。这确实允许多个子表引用父表中的同一行，但这仍然是最好的折衷方案。 - Bill Karwin

Answer 2

“一张大表使生活更轻松”：我见过这种情况的自然结果，就是一个有100多列的表格，我可以告诉你，我不喜欢使用这种表格。

主要问题在于这种表格的设计者往往省略了确保数据完整性所需的约束条件。例如，OP说：

“期刊参考文献需要期刊标题和文章标题，还需要页码，而书籍则需要出版商和出版日期，但期刊文章不需要。”

...这意味着以下约束条件：

CONSTRAINT a_journal_must_have_a_journal_title
   CHECK ( type <> 'journal' OR journal_title IS NOT NULL );

CONSTRAINT a_journal_must_have_an_article_title 
   CHECK ( type <> 'journal' OR article_title IS NOT NULL );

CONSTRAINT a_journal_must_have_a_page_number 
   CHECK ( type <> 'journal' OR page_number IS NOT NULL );

CONSTRAINT a_journal_cannot_have_a_publisher 
   CHECK ( type <> 'journal' OR publisher IS NULL );

CONSTRAINT a_journal_cannot_have_a_publication_date 
   CHECK ( type <> 'journal' OR publication_date IS NULL );

CONSTRAINT a_book_cannot_have_a_journal_title 
   CHECK ( type <> 'book' OR journal_title IS NULL );

CONSTRAINT a_book_cannot_have_a_article_title 
   CHECK ( type <> 'book' OR article_title IS NULL );

CONSTRAINT a_book_cannot_have_a_page_number 
   CHECK ( type <> 'book' OR page_number IS NULL );

CONSTRAINT a_book_must_have_a_publisher 
   CHECK ( type <> 'book' OR publisher IS NOT NULL );

CONSTRAINT a_jbook_must_have_a_publication_date 
   CHECK ( type <> 'book' OR publication_date IS NOT NULL );

我怀疑这只是冰山一角！

我希望在编写了几百个这样的约束之后，设计人员会重新考虑所有那些可空列 :)

- onedaywhen

Answer 3

我的建议是要从正确设计数据库开始，使用规范化确保表只包含有关一件事情（书籍、期刊等）的数据，并将属性存储在正确的表中。

如果将来出现性能问题，您可以将其非规范化成较少的表，但这不太可能成为问题，除非您拥有庞大的数据库。

创建一个表来保存所有参考文献共同的属性。

创建单独的表来保存每种类型参考文献特定的属性。

另一个问题是是否会有许多参考文献指向同一作品，例如针对某个期刊的数百个引用。规范化将建议您拥有一个表，其中包含期刊的信息（标题、作者、期刊），一个表，其中包含特定于期刊的参考信息（文章、页码），以及另一个表，其中包含所有参考文献通用的数据（引用日期、引用类型）。

- James Piggot

---

噢！提出了一些我没有考虑到的有趣问题。谢谢 :-) - robintw

Answer 4

如果只有一个带有“类型”字段的表，在添加需要额外字段的新引用类型时会出现问题。扩展类型字段值没有问题，但您必须向表中添加列，为所有当前行填充默认值等。

拥有单独的表将使添加新的引用类型变得非常容易（并自动生成相应表单！），而搜索也不会更加困难。

- Marcin Gil

Answer 5

Rails支持单表继承和多态ActiveRecord类型。建议您了解这些内容-ActiveRecord对数据库的结构有一些看法。

- tobyhede

---

我认为这是正确的想法。单表继承模式并不仅限于Rails。 - Matt Brown

Answer 6

我认为你需要预先考虑每个解决方案的SQL查询语句。如果你这样做，你会发现把所有东西都放在一个表中编写最容易，并且可能会得到最佳性能。从一个表中分离出你想要的东西比从多个表中组合更容易。

假设我的大表看起来像这样：

1 id 2 类型 3 书和期刊共有的字段 4 书特有的字段 5 期刊特有的字段

如果我只对书感兴趣，我可以创建一个视图，或者像这样使用普通的SQL：

create view book as  
select id, field_common-to-book-and-journal, field-specific-to-book
from my-one-big-table
where type = 'book'

因此，当我需要时，很容易模拟数据在单独的表中。

但是，如果我一开始就把数据放在不同的表中，那么我最终将编写如下SQL：

select id, field-common-to-book-and-journal from books
union
select id, field-common-to-book-and-journal from journal-articles
union
.... etc, for each type

我不知道其他数据库的情况，但在SQL Server中进行联合操作可能会很昂贵，并且在使用ntext等数据类型时存在限制。

如果您遵循olavk的建议，则组合多种类型的SQL查询将如下所示：

select 
    common.id, 
    common.field-common-to-book-and-journal, 
    book.field-specific-to-book 
    journal.field-specific-to-journal
from common-table common
left outer join book-specific-table book on 
left outer join journal-specific-table journal on
... etc, for each type

我曾经使用过这三种方式的系统，其中一个大表格的方式是最容易的。

- Corey Trager

---

我完全同意。您还可以根据类型添加一些约束，使某些列对于特定类型是必需的。这些约束缓解了“所有内容都可为空”的问题。 - Mat Roberts

Mat：如何基于类型设置约束？这可以在数据库本身中完成，还是必须由应用程序控制？ - robintw

当然，SQL的外观并不重要，您可以将其放入视图中，并以这种方式处理它。 - jammus

jammus - 如果只是外观问题，我同意，但就在前几天，当我尝试使用ntext进行联合操作时被卡住了...Steven - 我的意思是union（并集）。如果你将苹果和橙子分开，但希望看到所有水果，你需要使用并集。 - Corey Trager

如果您的典型查询仅引用Common表中的属性，则多表解决方案是可行的。 - Bill Karwin

Answer 7

很大程度上取决于您有多少不同的字段和字段大小，您对总行大小有限制（可以在一定程度上忽略此限制，因为永远不会填充所有字段，但是一旦页面过宽，实际存储在数据库中的信息将分割，从而使检索时间加长。 因此，如果信息很小并且（这很重要）不太可能发生变化（很少需要添加新类型的尚未考虑的信息），则单个表是更好的选择。 如果表格太宽，或者可能面临许多可能更改存储数据类型的情况，则分开的表格将是更好的方法，尽管始终很难正确查询。 如果您经常希望同时查询多种类型的引用，则大表是更有效的方法。 如果通常只需要一次抓取一个，则使用连接效率不高。

如果您选择使用单个表路由，请确保在表上放置触发器，以强制执行每种数据类型的数据完整性规则。 您将需要此功能，因为您不能依赖于使字段必填。

具有单独表格的一个问题是，在运行时，您不知道需要加入哪个表格。 这使您处于动态SQL领域，我不喜欢（出于安全性，效率和维护原因），或者使您执行左连接以加入可能需要或不需要的表格，这是低效的。

另一个可能性是将整个引用字符串存储在一个更大的字段中，并使用用户界面检查以确保所有必需部分都存在，然后连接记录并将信息发送到数据库。 对于大多数想要所有信息的查询来说，这将是最快的，但是如果您需要只提取一些数据，则会很麻烦。 它还依赖于所有数据都通过用户界面插入，这可能是您需要单独拆分此信息的情况。 但是说实话，我看不出您为什么需要将此信息分开，因此这是我可能采取的方法。 但是我不知道您的业务规则，所以请谨慎考虑。

- HLGEM

Answer 8

我不认为连接表格特别繁琐；在这里，我会采取更规范化的方法。

- peacedog

Answer 9

还有另一种选择：虽然我不完全支持，但这仍然是另一种选择：

使用三个表：

refs (id, title, refType)
-- title of the reference, and what type of reference it is

fieldDef (id, fieldName, refType, dataType)
-- name of the field, which reference types it applies to, and
-- what type of data is stored in these fields (ISDN number, date, etc)

fields (refId, fieldId, value)
-- where you actually add data to the references.

refType 可以是引用类型，如果将其设置为整数，并且值按2的幂增加（1、2、4、8...），则可以将它们相加以在 fieldDef 表中生成位掩码。

优点：非常简单和可扩展。如果您想到另一种引用类型或现有引用类型的新字段类型，则可以快速添加。每个引用类型的表单可以自动生成。所有数据都存储在一个地方，这意味着您不需要跟踪多个模式CRUD 操作。

缺点：这就是 The Daily WTF 的内容。选择语句可能会变得非常混乱和复杂。数据库无法执行类型检查（例如：对于日期等），通用的“value”字段不会针对存储在其中的数据进行优化。

- nickf

---

非常有趣的想法 - 但我可以看出它会导致TheDailyWTF！ - robintw

我无法告诉你我们公司在2000年代初决定使用极端版本的代价有多大。结果基础设施可能只被5名员工理解。随后多年内简单事情未完成所带来的机会成本是巨大的！ - 6eorge Jetson

好的 - 那我肯定不会使用这个选项！不过还是谢谢你的建议。 - robintw

这被称为实体-属性-值，是一个可怕的想法。而且由于原始问题只有几个不同的子类型，EAV 会过度设计。 - Bill Karwin

相反，我会说一个只有几种类型的系统是这种方法的最佳候选者。我确实不会将其用于任何比参考文献数据库更大的东西。 - nickf

Answer 10

我曾经与我的上级讨论过这些问题。当然，我无法证明“分层多表方法”（见olavk的回答）更好，但我感觉它更好！我总是选择这种方法。一个根表格包含所有实体共有的字段，以及具有不同字段的1-1子表格。如有需要，此方法可以扩展到更多子表格，只要业务逻辑和其他实体能从中获益。也就是说，我认为不需要过度使用此方法。

我也反对在没有根表的情况下创建分离的“子”表，其中每个表都有相同字段的副本。我认为 Corey's answer将此方法作为不良多表模型的示例，并且还批评了它。我想补充一点的是，写连接语句并不是主要问题。这根本不是问题，因为大多数数据库查询都有许多连接，这是正常的事情。难点在于与其他表建立关系-您始终需要Id和TypeId才能知道哪个表与之相关。对于根表，您只需要Id。

- Dragoljub Ćurčić