database - 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

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.