In a StackOverflow clone, what relationship should a Comments table have to Questions and Answers?

Question

In an application similar to StackOverflow that I am building, I am trying to decide what relationship my Questions, Answers and Comments tables should have.

I could have Questions and Answers both be represented by a single table Posts.

That would allow Comments to have a single foreign key to Posts.

But if Questions and Answers are separate tables, what relationships should Comments have to each of these?

UPDATE: Although the chosen answer recommends a Class Table Inheritance approach and this seems like the best approach in database terms, this option is not supported by the Rails ORM. So, in Rails my models will have to use Single Table Inheritance and will probably look like this:

class Post < ActiveRecord::Base  
end  

class Question < Post  
  has_many :answers, :foreign_key => :parent_id  
  has_many :comments, :foreign_key => :parent_id  
end  

class Answer < Post  
  belongs_to :question, :foreign_key => :parent_id  
  has_many :comments, :foreign_key => :parent_id  
end  

class Comment < Post  
  belongs_to :question, :foreign_key => :parent_id  
  belongs_to :answer, :foreign_key => :parent_id  
end


class CreatePosts < ActiveRecord::Migration  
    def self.up  
      create_table :posts do |t|  
        t.string :type 
        t.string :author   
        t.text :content  
        t.integer :parent_id   
        t.timestamps  
      end  
    end  


    def self.down  
      drop_table :posts  
    end  
end

CREATE TABLE "posts" (
  "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,  
  "type" varchar(255),  
  "author" varchar(255),  
  "content" text,  
  "parent_id" integer,  
  "created_at" datetime, 
  "updated_at" datetime
  );

Answer 1

You would need two domain tables that bring the relationships together CommentsForQuestions and CommentsForAnswers. Basically you're going to need to create 5 tables for this purpose:

Questions
Answers
Comments
CommentsForQuestions (relates comments to questions)
CommentsForAnswers (relates comments to answers)

The one problem this has is that it's inferior to the posts idea because referential integrity isn't as strong. I can garuntee that CommentsForQuestions connects to a comment and a question, but I can't prevent both a question and an answer from connecting to the same comment.

Answer 2

A foreign key relationship; you can either have QuestionComments and AnswerComments, or you can have Comments have a Foreign key column for both Questions and Answers (and have those columns be exclusive).

Personally, I'd go with the Posts approach.

Edit: On consideration, there's a third approach that might work; you might have a Comments table, and then just have an association table that associates the Comments with either a Question or an Answer (so Comments would have an ID and the comment, the join table would have a CommentID, an AnswerID, and a QuestionID). Or, you could have just a Comments table, then have an Answer-Comment association table, and a separate Question-Comment association table.

Answer 3

You could create a single comments table with two foreign keys, one to questions.questionID and the other to answers.answerId

Answer 4

I'd go with the Posts approach. This is the best way to ensure referential integrity.

If you need additional columns for Answers and Questions respectively, put them in additional tables with a one-to-one relationship with Posts.

For example, in MySQL syntax:

CREATE TABLE Posts (
  post_id     SERIAL PRIMARY KEY,
  post_type   CHAR(1),              -- must be 'Q' or 'A'
  -- other columns common to both types of Post
  UNIQUE KEY (post_id, post_type) -- to support foreign keys
) ENGINE=InnoDB;

CREATE TABLE Comments (
  comment_id  SERIAL PRIMARY KEY, 
  post_id     BIGINT UNSIGNED NOT NULL,
  -- other columns for comments (e.g. date, who, text)
  FOREIGN KEY (post_id) REFERENCES Posts(post_id)
) ENGINE=InnoDB; 

CREATE TABLE Questions (
  post_id     BIGINT UNSIGNED PRIMARY KEY,
  post_type   CHAR(1),              -- must be 'Q'
  -- other columns specific to Questions
  FOREIGN KEY (post_id, post_type) REFERENCES Posts(post_id, post_type)
) ENGINE=InnoDB;

CREATE TABLE Answers (
  post_id     BIGINT UNSIGNED PRIMARY KEY,
  post_type   CHAR(1),              -- must be 'A'
  question_id BIGINT UNSIGNED NOT NULL,
  -- other columns specific to Answers
  FOREIGN KEY (post_id, post_type) REFERENCES Posts(post_id, post_type)
  FOREIGN KEY (question_id) REFERENCES Questions(post_id)
) ENGINE=InnoDB;

This is called Class Table Inheritance. There's a nice overview of modeling inheritance with SQL in this article: "Inheritance in relational databases."

It can be helpful to use post_type so a given Post can be only one answer or one question. You don't want both an Answer and a Question to reference one given Post. So this is the purpose of the post_type column above. You can use CHECK constraints to enforce the values in post_type, or else use a trigger if your database doesn't support CHECK constraints.

I also did a presentation that may help you. The slides are up at http://www.slideshare.net/billkarwin/sql-antipatterns-strike-back. You should read the sections on Polymorphic Associations and Entity-Attribute-Value.

---

If you use Single Table Inheritance, as you said you're using Ruby on Rails, then the SQL DDL would look like this:

CREATE TABLE Posts (
  post_id     SERIAL PRIMARY KEY,
  post_type   CHAR(1),              -- must be 'Q' or 'A'
  -- other columns for both types of Post
  -- Question-specific columns are NULL for Answers, and vice versa.
) ENGINE=InnoDB;

CREATE TABLE Comments (
  comment_id  SERIAL PRIMARY KEY, 
  post_id     BIGINT UNSIGNED NOT NULL,
  -- other columns for comments (e.g. date, who, text)
  FOREIGN KEY (post_id) REFERENCES Posts(post_id)
) ENGINE=InnoDB;

You can use a foreign key constraint in this example, and I recommend that you do! :-)

Rails philosophy tends to favor putting enforcement of the data model into the application layer. But without constraints enforcing integrity at in the database, you have the risk that bugs in your application, or ad hoc queries from a query tool, can harm data integrity.

Answer 5

There are 2 ways that I can think of.

First, use another column in the Comment table to indicate whether the comment belongs to Question or Answer. So the PK of the Comment table becomes wher PostID is the foreign key to Question or Answer and PostType can be someting like 1=Question and 2=Answer.

Second, use a relationship table for each Question and Answer. So you have a Question, Answer, Comment, QuestionComment, and AnswerComment table.

Let's say the primary key of Question, Answer, Comment tables are QuestionID, AnswerID, and CommentID respectively. Then the columns of QuestionComment would be [QuestionID, CommentID]. Similarly, the columns of AnswerComment would be [AnswerID, CommentID].

Answer 6

In the social networks that I build I do something a bit different. If you think about it a comment could be attached to just about any Entity in a site. This could be a blog post, a forum thread or post, an article, someones picture, a persons profile, a vendor of a service, etc. For this reason I create a SystemObjects table which holds the object type (table reference). For the most part I create records for the Entities of my system that will accept comments...but these map directly to my tables. The SystemObjects table houses the SystemObjectID, and a friendly name for future reference (a lookup table).

With this in place I then create a Comments table which has the SystemObjectID reference to tell me what table to go look in. Then I also house the SystemObjectRecordID which tells me which PK of the referenced table I am interested in (along with all the standard comment data).

I use this notion of the SystemObject table for many other generic far reaching concepts in my sites. Think about Tags, Ratings, Comments, and any other dangling fruit that might be attached across your site and aggregated up for quick use.

Read more about this in my book ASP.NET 3.5 Social Networking.