What type of Rails model relationship would work best for aggregate keys + multiple foreign keys?

Question

I'm considering the following database structure but I'm not sure what type of Rails model relationships would support the database keys I have defined. Could anyone suggest how this might work in Rails?

Posts
id
post_type -- must be 'Q' or 'A'
author
date
content
UNIQUE KEY (post_id, post_type) -- to support foreign keys

Questions
id
post_id
post_type -- must be 'Q'
FOREIGN KEY (post_id, post_type) REFERENCES Posts(post_id, post_type)

Answers    
id
post_id
post_type -- must be 'A'
question_id
FOREIGN KEY (post_id, post_type) REFERENCES Posts(post_id, post_type)
FOREIGN KEY (question_id) REFERENCES Questions(post_id)

Comments    
id
post_id
author
date
content
FOREIGN KEY (post_id) REFERENCES Posts(post_id)

The above sketch would translate to the following implementation:

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;

Answer 1

You have several options as to how to model this within rails, however the first thing I would suggest is that in order to save yourself time and trouble later you should approach your question from a different angle.

In order to get the most from Rails you should not start with a database design. You should begin with a Data Model and then look at the way to map this Data model to a database structure not the other way around. This is a subtle difference but involves a different mindset where you are seeing your database as a secondary consideration to your model, not the other way around. This will make the problem easier to understand in the long term.

There are two ActiveRecord constructs that could be used within this scenario, they are Single Table Inheritance and Polymorphic inheritance.

Single Table Inheritance

Single Table Inheritance (STI) stores models with much shared functionality within the same underlying database table. In your example questions and answers, and to a lesser extent comments are all similar objects. They have some content, an author, some datetime fields for created_at and updated at etc. The only difference between a question and an answer is that questions "belong to" an answer. Comments are slightly trickier as you can comment on both Questions and answers, and maybe also on comments although your Database Schema does not reflect that this is possible.

With STI your Question and Answer models are not stored within a separate table but within a single table and marked with the class names. The actual Question and Answer classes then inherit from the Base class, in your case "Post". There are numerous resources ou there discussing STI but this one may help

Polymorphic Inheritance

This is the second method of modelling similar behaviour within rails. This uses a single table, in your case posts to store the data that is common between the two classes. This table contains columns that reference the class name and the id instance of the base object. The data specific to the object would then be stored in a separate table per model.

Implementation (using STI)

To model your data using STI then you would create a base model of posts like this

  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

Your models would then 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

And some sample code

q1 = Question.new(:author => 'Steve', :content => 'What is 2 + 2')  
q1c1 = q1.comments.build(:author => 'Malcolm', 
    :content => "Good question, i'd been wondering that myself")    
q1a1 = q1.answers.build(:author => 'John', :content => '2+2 = 5')  
q1a2 = q1.answers.build(:author => 'Phil', :content => '2+2 is a sum')  

q1a1c1 = q1a1.comments.build(:author => 'Chris', 
    :content => 'Sorry John it should be 4')  
q1a2c1 = q1a2.comments.build(:author => 'Steve', 
    :content => 'Hi Phil thanks for stating the obvious!')  

q1.save  

qu = Question.find(:first)  
puts "#{qu.author} asked #{qu.content}"  
qu.comments.each {|qc| puts "\t#{qc.author} commented #{qc.content}"}  
qu.answers.each do |ans|  
  puts "\t#{ans.author} answered with #{ans.content}"  
  ans.comments.each do |comm|   
    puts "\t\t#{comm.author} commented #{comm.content}"   
  end  

end

This code produces the following results

Steve asked What is 2 + 2
  Malcolm commented Good question, i'd been wondering that myself
  John answered with 2+2 = 5
    Chris commented Sorry John it should be 4
  Phil answered with 2+2 is a sum
    Steve commented Hi Phil thanks for stating the obvious!

Looking in the database, there is a single posts table with the following structure

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
  );

And the data content after the example is as follows :-

1|Question|Steve|What is 2 + 2||2009-06-13 09:52:20|2009-06-13 09:52:20
2|Answer|John|2+2 = 5|1|2009-06-13 09:52:20|2009-06-13 09:52:20
3|Comment|Chris|Sorry John it should be 4|2|2009-06-13 09:52:20|2009-06-13 09:52:20
4|Answer|Phil|2+2 is a sum|1|2009-06-13 09:52:20|2009-06-13 09:52:20
5|Comment|Steve|Hi Phil thanks for stating the obvious!|4|2009-06-13 09:52:20|2009-06-13 09:52:20
6|Comment|Malcolm|Good question, i'd been wondering that myself|1|2009-06-13 09:52:20|2009-06-13 09:52:20

You may find it easier to split the comments model into QuestionComments and AnswerComments. This would make straight sql much easier.