在 StackOverflow 克隆中，Comments 表应该与 Questions and Answers 有什么关系? [英] In a StackOverflow clone, what relationship should a Comments table have to Questions and Answers?

问题描述

在我正在构建的类似于 StackOverflow 的应用程序中，我试图确定我的 Questions、Answers 和 Comments 表应该有什么关系有.

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.

我可以让 Questions 和 Answers 都用一个表 Posts 表示.

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

这将允许 Comments 有一个外键到 Posts.

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

但是如果 Questions 和 Answers 是单独的表格，Comments 应该与其中的每一个有什么关系?

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

更新:虽然选择的答案推荐了类表继承方法，这似乎是数据库术语中的最佳方法，但 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
  );

推荐答案

我会采用 Posts 方法.这是确保参照完整性的最佳方式.

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;

这称为类表继承.在这篇文章中有一个很好的 SQL 建模继承概述:关系数据库中的继承."

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-反模式反击.您应该阅读有关多态关联和实体-属性-值的部分.

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 克隆中，Comments 表应该与 Questions and Answers 有什么关系?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！