额外的表或非特定的外键？
系统中有多种类型的对象，每种对象在数据库中都有自己的表。 用户应该能够对其中任何一个进行评论。 您将如何设计评论表？ 我可以想到几个选项：
- 一个注释表，每个对象类型都有一个 FK 列（ObjectAID、ObjectBID 等）
- 多个注释表，每个对象类型一个（ObjectAComments、ObjectBComments 等）
- 一个通用 FK (ParentObjectID)，其中另一列指示类型（“ObjectA”）
您会选择哪一个？ 还有我没有想到的更好的方法吗？
There are several types of objects in a system, and each has it's own table in the database. A user should be able to comment on any of them. How would you design the comments table(s)? I can think of a few options:
- One comments table, with a FK column for each object type (ObjectAID, ObjectBID, etc)
- Several comments tables, one for each object type (ObjectAComments, ObjectBComments, etc)
- One generic FK (ParentObjectID) with another column to indicate the type ("ObjectA")
Which would you choose? Is there a better method I'm not thinking of?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
我喜欢做的一件事是拥有一个单独的表，将通用/公用表链接到所有个性化表。
因此，对于对象 Foo 和 Bar，然后对 Foo 和 Bar 进行注释。 酒吧，你会得到这样的东西：
这种结构：
One of the things I like to do is have a separate tables that link the generic/common table to all of the individualized tables.
So, for objects Foo and Bar and then comments on Foo & Bar, you'd have something like this:
This structure:
设计架构以使可注释（由于缺乏更好的词）表遵循标准继承建模模式之一是否可行？ 如果是这样，您可以让注释表的 FK 指向公共父表。
Is it feasible to design the schema so that the commentable (for lack of a better word) tables follow one of the standard inheritance-modeling patterns? If so, you can have the comment table's FK point to the common parent table.
@Hank Gay
所以类似：
@Hank Gay
So something like:
请小心不完全指向一张表的通用外键。 如果必须拆分类型上的 where 条件并指向多个不同的表，查询性能会受到极大影响。 如果您只有几种类型，并且类型的数量不会增长，那么可以为不同的表设置单独的可为空的外键，但如果您有更多类型，最好提出不同的数据模型（例如@palmsey 的建议）。
Be careful with generic foreign keys that don't point to exactly one table. Query performance suffers dramatically if you have to split the where condition on a type and point to several different tables. If you only have a few types, and the number of types will not grow, it's Ok to have separate nullable foreign keys to the different tables, but if you will have more types, it's better to come up with a different data model (like @palmsey's suggestion).
@palmsey
差不多了，但是我最常看到的该模式的变化摆脱了
ObjectAID等。ParentID成为Parents的 PK 和 FK。 这会让你得到类似的东西：ParentsParentIDObjectAParentID（FK 和 PK）ColumnFromA NOT NULLObjectBParentID（FK 和 PK）ColumnFromB NOT NULLComments将保持不变。 然后，您只需要限制 ID 生成，这样您就不会意外地得到一个ObjectA行和一个ObjectB行，它们都指向相同的Parents< /代码> 行； 最简单的方法是对ObjectA和ObjectB使用与Parents相同的序列（或其他序列）。您还可以看到很多模式，例如：
ParentsID子类鉴别器ColumnFromA（可为空）ColumnFromB（可为空）和
注释将保持不变。 但现在，如果不编写触发器或在不同的层执行此操作，则无法强制执行所有业务约束（子类的属性均可为空）。@palmsey
Pretty much, but the variation on that pattern that I've seen most often gets rid of
ObjectAIDet al.ParentIDbecomes both the PK and the FK toParents. That gets you something like:ParentsParentIDObjectAParentID(FK and PK)ColumnFromA NOT NULLObjectBParentID(FK and PK)ColumnFromB NOT NULLCommentswould remain the same. Then you just need to constrain ID generation so that you don't accidentally wind up with anObjectArow and anObjectBrow that both point to the sameParentsrow; the easiest way to do that is to use the same sequence (or whatever) that you're using forParentsforObjectAandObjectB.You also see a lot of schemas with something like:
ParentsIDSubclassDiscriminatorColumnFromA (nullable)ColumnFromB (nullable)and
Commentswould remain unchanged. But now you can't enforce all of your business constraints (the subclasses' properties are all nullable) without writing triggers or doing it at a different layer.