数据库设计 - 一个表中的列引用两个表
这是我所拥有的一个示例（以 Stack Overflow 为例）。我有 2 个表格，问题 和答案。我还有一个 Comments 表。评论表将引用问题和答案。
我应该如何设置数据库？评论中有 2 列，即 QuestionId 和 AnswerId。有一张桌子可以同时容纳问题和答案吗？中间有一张桌子以某种方式告诉我问题或答案吗？
编辑：找到了SO数据浏览器，它使用一张表来存储问题和答案...我只是不喜欢POSTS表中有这么多NULL。这是否有任何负面影响，例如性能？
Here is an example of what I have (take Stack Overflow). I have 2 tables, Questions and Answers. I also have a Comments table. The Comments table will reference both Questions and Answers.
How should I set up the database? Have 2 columns in Comments, a QuestionId and AnswerId. Have one table for both Questions and Answers? Have a table in between that somehow tells me Question or Answer?
EDIT: Found the SO Data explorer, it uses one table for both Questions and Answers ... I just don't like the POSTS table having so many NULLS in it. Does that have any negative effects, like on performance?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
StackOverflow 将问题和答案建模为同一实体：
POSTS。除了指示接受/授予的答案之外，它们具有相同的属性。评论拥有自己的表，并使用外键（
post_id）与相应的帖子相关。无需加载每月的 SO 转储，您可以通过 StackExchange 查看（和查询）SO 架构数据浏览器。
StackOverflow models the questions and answers as being the same entity:
POSTS. They have identical properties, aside from indicating the answer where accepted/granted.Comments get their own table, and relate to the respective post using a foreign key -- the
post_id.Without needing to load the monthly SO dumps, you can view (and query) the SO schema via the StackExchange Data Explorer.
创建另一个关系
Post_Type来跟踪Comments表中的 ID 是问题还是答案。然后，在您的
Comments表中，除了跟踪您的问题 Id/答案 Id 的属性之外，还添加Post_Type.post_type_id作为外键。Comments表中不需要两列 (QuestionId + AnswerId)，因为这会在场景问题域中的任一列中提供null。干杯。
Create another relation
Post_Typeto keep track of whether the ID in yourCommentstable is a question or an answer.Then in your
Commentstable, addPost_Type.post_type_idas the foreign key, in addition to the attribute that keeps track of your Question Id/Answer Id.You won't need two columns (QuestionId + AnswerId) in the
Commentstable, because that will give younullin either columns, in your scenario problem domain.Cheers.
您的意思是您可以对问题和答案发表评论？您将有 5 个表：
表
question_comment将有一个comment_id和一个question_id。answer_comment的处理类似。You mean you can have comments on both questions and answers? You will have 5 tables:
The table
question_commentwill have acomment_idand aquestion_id. Similar deal withanswer_comment.