在StackOverflow克隆中，注释表与问题和解答有什么关系？ [英] In a StackOverflow clone, what relationship should a Comments table have to Questions and Answers?

问题描述

在类似于StackOverflow的应用程序，我正在建立，我试图决定什么关系我问题， Answers 表格应该有

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.

我可以有问题和答案都由单个表 Posts 表示。

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.

但如果问题和答案是单独的表， c>

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

更新：尽管选择的答案建议使用Class Table Inheritance方法，这似乎是最好的方法在数据库术语中，Rails ORM不支持此选项。所以，在Rails我的模型将必须使用单表继承，可能看起来像这样：

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

推荐答案

这是确保引用完整性的最好方法。

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.

例如，在MySQL语法中：

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;

这称为Class Table Inheritance。本文中有一个关于使用SQL建模继承的概述：关系数据库中的继承< a>。

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

使用post_type是有帮助的，所以给定的帖子只能有一个答案或一个问题。你不想要一个答案和一个问题引用一个给定的职位。所以这是上面 post_type 列的目的。您可以使用CHECK约束来强制实施 post_type 中的值，或者如果数据库不支持CHECK约束，请使用触发器。

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.

我也做了一个演示，可能会帮助你。幻灯片在 http://www.slideshare.net/billkarwin/sql上-antipatterns-strike-back 。您应该阅读关于多态关联和实体属性值的部分。

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.

如果使用单表继承，表示您使用的是Ruby on Rails，则SQL DDL将如下所示：

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哲学倾向于将数据模型的强制执行到应用程序层。但是没有在数据库中强制执行完整性的约束，您的应用程序中的错误或查询工具的即席查询可能会损害数据完整性。

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.

这篇关于在StackOverflow克隆中，注释表与问题和解答有什么关系？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！