database - 在 StackOverflow 克隆中，Comments 表与 Questions and Answers 应该有什么关系？

Question

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

我可以让 Questions 和 Answers 都由一个表 Posts 表示。

这将允许 Comments 有一个指向 Posts 的外键。

但是如果Questions 和Answers 是单独的表，那么Comments 应该与它们中的每一个有什么关系？

更新:虽然选择的答案推荐类表继承方法，这似乎是数据库方面的最佳方法，但 Rails ORM 不支持此选项。因此，在 Rails 中，我的模型将不得不使用单表继承并且可能看起来像这样:

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

我会选择 Posts 方法。这是确保参照完整性的最佳方式。

如果您需要分别为 Answers 和 Questions 添加额外的列，请将它们放在与 Posts 具有一对一关系的额外表中。

例如，在MySQL语法中:

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 建模继承有一个很好的概述:“Inheritance in relational databases 。”

使用 post_type 会很有帮助，因此给定的帖子只能是一个答案或一个问题。您不希望答案和问题都引用一个给定的帖子。所以这就是上面 post_type 列的目的。您可以使用 CHECK 约束来强制执行 post_type 中的值，或者如果您的数据库不支持 CHECK 约束，则使用触发器。

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

---

如果您使用单表继承，正如您所说的，您使用的是 Ruby on Rails，那么 SQL DDL 将如下所示:

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; 

您可以在此示例中使用外键约束，我建议您这样做! :-)

Rails 哲学倾向于将数据模型的实现放入应用程序层。但是，如果没有在数据库中强制执行完整性的约束，您的应用程序中的错误或来自查询工具的临时查询可能会损害数据完整性。