Database Design - Column In One Table References Two Tables

Question

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?

Answer 1

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.

Answer 2

Create another relation Post_Type to keep track of whether the ID in your Comments table is a question or an answer.

post_type varchar(20) NOT NULL,
post_type_id tinyint PRIMARY KEY,

Then in your Comments table, add Post_Type.post_type_id as 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 Comments table, because that will give you null in either columns, in your scenario problem domain.

Cheers.

Answer 3

You mean you can have comments on both questions and answers? You will have 5 tables:

question
answer
comment
question_comment
answer_comment

The table question_comment will have a comment_id and a question_id. Similar deal with answer_comment.