Databases
StackOverflow2013
Postsdbo
POIn a StackOverflow clone, what relationship should a Comments table have to Questions and Answers?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POIn a StackOverflow clone, what relationship should a Comments table have to Questions and Answers?
    text
    copied!<p>In an application similar to StackOverflow that I am building, I am trying to decide what relationship my <code>Questions</code>, <code>Answers</code> and <code>Comments</code> tables should have.</p> <p>I could have <code>Questions</code> and <code>Answers</code> both be represented by a single table <code>Posts</code>.</p> <p>That would allow <code>Comments</code> to have a single foreign key to <code>Posts</code>.</p> <p>But if <code>Questions</code> and <code>Answers</code> are separate tables, what relationships should <code>Comments</code> have to each of these?</p> <p>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:</p> <pre class="lang-ruby prettyprint-override"><code>class Post &lt; ActiveRecord::Base end class Question &lt; Post has_many :answers, :foreign_key =&gt; :parent_id has_many :comments, :foreign_key =&gt; :parent_id end class Answer &lt; Post belongs_to :question, :foreign_key =&gt; :parent_id has_many :comments, :foreign_key =&gt; :parent_id end class Comment &lt; Post belongs_to :question, :foreign_key =&gt; :parent_id belongs_to :answer, :foreign_key =&gt; :parent_id end class CreatePosts &lt; 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 </code></pre> <pre class="lang-sql prettyprint-override"><code>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 ); </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload