数据库设计 - 一列中的列引用两个表 [英] Database Design - Column In One Table References Two Tables

问题描述

这里是我有一个例子（采取Stack Overflow）。我有2个表，问题和答案。我还有一个注释表。注释表将引用问题和答案。

如何设置数据库？在注释中有2列，一个QuestionId和AnswerId。有问题和答案的一个表吗？

编辑：找到SO Data Explorer，它为两个问题使用一个表格和答案...我只是不喜欢的 POSTS 表中有这么多的NULLS。

解决方案

StackOverflow将问题和答案建模为同一个实体： POSTS 。他们有相同的属性，除了指示接受/授予的答案。

注释获得自己的表，并使用外键与相应的帖子相关 - code> post_id 。

无需加载每月SO转储，您可以通过StackExchange数据资源管理器查看（和查询）SO模式。

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?

解决方案

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.

这篇关于数据库设计 - 一列中的列引用两个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！