多个表之间共享关系的最佳实践

Question

我一直遇到这个设计问题，到目前为止我对我的解决方案并不满意。问题是这样的：

我有两个或多个实体，例如人和狗，它们都与 Notes 表有关系，该表存储消息字段和有关消息的一些元数据（例如作者）。

1）第一个选择是不强制执行外键。这样我就可以将 FK 像 peopleId 或dogId（无论它是什么）存储在同一个通用 FK 字段（如 fkId）中。然后我将 tableId 存储在另一列中——人们可以希望从 RDMS 元数据中获取表 id，但您也可以进行肮脏的黑客攻击并显式地创建一个充满必须手动更新的表的表。这确实很草率，我只是为了完整性而提及它。

2) 为每个需要它的表克隆 Notes 表，例如 PeopleNotes、DogNotes、CatNotes 等。这会产生一个相当大的规范化问题。

其他人在这种情况下做了什么？

Answer 1

如果这些是您的“模型”表：

dog Table:
id | name | ...
1  | Rex
2  | Fido

people Table:
id | name | ...
1  | Bob
2  | Alice

notes Table:
id | text | ...
1  | A nice dog.
2  | A bad dog.
3  | A nice person.

您可以将关系保存在单独的表中：

dog_note Table:
dog_id | note_id
1      | 1
2      | 2

note_people Table:
person_id | note_id
1         | 3
2         | 3

我通常坚持使用模型的字母顺序来命名关系表的惯例。

Answer 2

两个新表——Dog2Notes 和 People2Notes 怎么样？ Dogs、People 和 Notes 都是带有彼此相关的键的条目。狗和人可以有多个笔记，并且笔记可以共享。

如果“狗”和“人”只能有一个注释，那么向每个表添加一个 NOTeID 会怎样？

Answer 3

我更喜欢将笔记的所有者存储在两列中的想法，一列用于 ID，一列用于类/表。

Answer 4

这实际上取决于您如何查询数据，但是像这样的事情怎么样，假设每个人/狗有多个注释：

PeopleTable

PeopleID
NoteID
.....

DogTable

DogID
NoteID
...

NoteTable

NoteID

NoteDetailTable

NoteDetailID
NoteID
NoteText
...

Answer 5

难道不是比目前建议的更好的解决方案是拥有一个主 id 表吗？

dog Table:
id | name | masterId
1  | Rex  |  1
2  | Fido |  4

people Table:
id | name | masterId
1  | Bob  |  2
2  | Alice|  3

masterId
id
1  
2  
3  
4  

notes
id | note       | masterId
1  | "Hi"       | 3
2  | "Good day" | 2

这将使可扩展性更容易，因为如果您需要添加新的实体类型（例如 cat），则不需要添加另一个表（cat_note），如果您添加新的笔记类型（例如 book），它特别有用，因为这样您需要为所有实体类型（person_book、dog_book 等）添加新表。最后，您可以直接将任何实体表与注释表关联起来。

唯一的“问题”是您需要运行一个过程，当新记录添加到实体表并将其与新条目关联时，该过程会自动将新记录添加到 masterId 表。

聚苯乙烯
我知道这个答案是在事实发生九个月后才得到的。刚刚在做其他研究时偶然发现了这一点，我想我投入了自己的两分钱。