数据库设计：我应该使用映射表吗？
以这样的问答网站为例。假设我有问题、答案和评论表。问题和答案都可以有多个评论。
最好是：
创建
QuestionComment和AnswerComment表来从问题/答案映射到评论（每个表都包含问题/答案 pk 和评论 pk）？或者我应该只让评论表包含 2 个可以为空的外键来提问和回答（其中一个外键始终为空，因为评论只能应用于单个“项目”）？
看起来 (1) 保持了引用完整性，而 (2) 则更紧凑。其中一个比另一个更受青睐吗？映射表是否应该仅为多对多关系保留？
Using a question and answer site like this as an example. Say I have question, answer, and comment tables. Questions and answers can each have multiple comments.
Would it be best to:
create
QuestionComment, andAnswerCommenttables to map from questions/answers to comments (each containing the question/answer pk and comment pk)?Or should I only have the comment table containing 2 nullable foreign keys to question and answer (one of which will always be null since a comment can apply only to a single "item")?
It seems like (1) maintains referential integrity while (2) is more compact. Is one preferred over the other? Should mapping tables be reserved only for many-to-many relationships?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您会以相同的方式使用这两个注释吗？如果是，则 (2) 否则 (1)
如果使用 (1)，则可以在两个表上创建一个视图以使它们显示为一个。
在情况 (2) 中，您可以添加一个触发器来强制每行仅填充一个外键列，或者按照 @Ronnis 建议使用 CHECK 约束（一种更好的技术）。
Will you use both comments in the same way? If so, then (2) otherwise (1)
If you use (1), you can create a view over both tables to make them appear as one.
In case (2), you can add a Trigger to enforce there being only one Foreign Key column being populated per row, or as @Ronnis suggested using a CHECK constraint (a better technique).
我在实践中见过这两种方法，但我更喜欢 (1) 多一点：
我认为 (1) 向浏览架构的人更清楚地表达您的域 - 他们会看到 Answer 和 AnswerComment 紧挨着。如果答案评论和问题评论位于同一个表中，您必须深入查看评论表以查看评论可以属于哪个对象。
如果您使用的是域对象和持久性分离的域模型，那么问题就毫无意义：将它们存储在同一个表或不同的表中并不重要。 （只有当它们的行为不同时，它们才会是单独的类。）
I've seen either approach done in practice, but I prefer (1) a little more:
I think (1) expresses your domain a little more clearly to someone browsing the schema - they'll see Answer and AnswerComment right next to each other. If answer comments and question comments are in the same table you have to drill down into the comment table to see to which object a comment can belong.
If you're using a domain model with separation between domain objects and persistence, the question is moot: it doesn't matter whether you store them in the same table or different tables. (And they would be separate classes only if they behave differently.)