使用子类型进行数据库设计还是不使用？

Question

我设计的数据库有三个主要表：BOOKS，ARTICLES和NOTES。每本书或文章可以有多个笔记，我的原始设计就是这样的，这意味着书上的笔记和文章上的笔记都进入“NOTES”表。以下是“NOTES”表的列：

note_id note_type note_type_id note_content

NOTE_TYPE可以是'book'或'article'；如果note_type为'book'，则NOTE_TYPE_ID是book_id的FK；如果note_type为'article'，则NOTE_TYPE_ID是article id。现在我开始怀疑这是否是正确（或最佳规范化）的设计。另一种方法是使用5个表： books / articles / notes / book_notes / article_notes

这样我就可以分别保留书籍笔记和文章笔记，列如下：

'notes' { note_id, note_content } 'book_notes' { book_id, note_id } 'article_notes' { articel_id, note_id }

哪一个是正确或更好的呢？

- Shawn

Answer 1

也许有一个略微不同的方法 -- supertype/subtype通常用于当你对于每个子类型都有非常特定的列时，例如在Person supertype中使用Patient和Doctor subtypes。 Person包含所有人类共有的数据，而Patient和Doctor则分别包含每个人的特定列。在这个例子中，你的book_notes和article_notes并没有太大的区别。
我更倾向于考虑将Publication作为supertype，将Book和Article作为subtypes。然后您可以只使用一个Note表，并具有到Publication的FK。考虑到Publication中的PK编号与Book（Article）的[PK、FK]编号相同，您可以在Publication、Book或Article上使用notes进行连接。这样，您可以简单地添加另一种出版物，比如杂志，仅通过添加一个新的子类表而不更改任何关于Note的内容。

例如：

TABLE Publication (
      ID (PK)
    , Title
    , -- more columns common to any publication
)

TABLE Book (
      ID (PK) = FK to Publication
    , ISBN
    , -- more columns specific to books only
)

TABLE Article (
    ID (PK) = FK to Publication
    , -- more columns specific to articles only)

TABLE Note (
      ID (PK)
    , PublicationID = FK to Publication
    , NoteText
)

Book和Article表的主键也作为对Publication表的外键。

现在，如果我们添加另一个出版物——杂志：

TABLE Magazine (
    ID (PK) = FK to Publication
    , -- more columns specific to magazines only
)

我们不需要以任何方式修改 Note -- 而且我们已经添加了仅适用于杂志的列。

---

- Damir Sudarevic

Answer 2

从某种角度来看，将书籍/书籍笔记/文章/文章笔记作为数据库设计原则，在长期运行中更好。

考虑到备份、数据操作和数据可移植性，将单个实体的属性放在自己的表中开始变得划算。

从绝对意义上说，两者都不是“更好”的选择，这取决于上下文。人们习惯于把任何东西放在合适的橱柜里，学术数据库设计师倾向于为每个牙刷创建一个橱柜。

在您的情况下，您可能会认为针对3个笔记表而不仅仅是一个表的SQL插入/选择/更新/删除的额外开销不值得。从长远来看，如果您最初采用“1个笔记表”设计，然后决定不喜欢它，将其拆分成3个并不像重写《战争与和平》那样困难。

- Steve De Caux

---

@Steve：优化对我来说并不是最重要的问题，我正在寻找一个更明智的模式，一个在概念上更好的模式。这就是我所说的“更好”。但是，由于book->notes * article->notes都是一对多关系，这就是我不太确定的地方，因为对于一对多关系，一个表似乎足以容纳它。 - Shawn

@Shawn：“概念上”可能有点棘手，正如您收到的其他答案所示。如果您指的是一个“更合理的模式”，即数据模型非常明显的模式，则应该采用books、book_notes、articles、article_notes的方式，其中“？_notes”表与其对应实体的PK相关联。我知道很多人认为这是表的重复，但它确实使数据模型非常容易理解。 - Steve De Caux

Answer 3

NOTE_TYPE 可以是 'book' 或 'article'; NOTE_TYPE_ID 是一个 book_id 的外键，如果 note_type 是 'book'，或者是 article_id 如果 note_type 是 'article'。

当在逻辑数据模型上表示时，这种关系被称为弧。

如果您不预见到任何笔记重复，那么这很好。不仅是在书籍之间，而且在文章之间也是如此。

- OMG Ponies

Answer 4

这取决于您想如何使用子类型。在您的主表中，书籍和文章似乎是“出版物”的子类型。但是，没有“出版物”表。这是因为您不需要搜索出版物，还是因为您没有考虑“一般化-特殊化关系建模”？如果您在网上查找此短语，您会看到一些有关此主题的好文章。

假设您不需要一个广义的“出版物”表，那么您可能也不需要一个广义的“笔记”表。您是否要搜索笔记，而无论笔记所涉及的出版物种类如何都无所谓？多久之后您想添加第三或第四种出版物？

所有这些都对“概念上更好”的设计产生影响。如果您想要概念上更好的东西，那么您正在优化，无论您是否意识到它。您可能正在针对与速度或简单性不同的良好度量进行优化。

- Walter Mitty

Answer 5

看起来你的主要关注点应该是笔记。鉴于这种情况，我会创建一个超类型-子类型数据结构。

笔记将包含使笔记独特的所有内容（超类型），以及仅限于所有书籍和文章中共同的项目（子类型）。

笔记超类型字段：

• 笔记ID
• 笔记内容
• 笔记类型ID（1=书籍 2=文章）

共同子类型字段：

• 标题
• 作者
• 日期

书籍独特字段：（NoteTypeId=1）

• ISBN
• 出版商
• 价格
• 等等

文章独特字段：（NoteTypeId=2）

• 网站
• 分发权利
• 等等

这使人们可以通过内容、类型、标题、作者和日期搜索或浏览所有的笔记。然后，为了获取更多信息，您可以深入到子类型的详细信息中。

这也允许增长，因此您可以根据需要轻松添加其他子类型。例如博客（NoteTypeId=3）、Facebook页面（NoteTypeId=4）等。

- Michael Riley - AKA Gunny

---

这是一个有趣的概念模型。物理模型看起来像什么？ - Stephanie Page