数据库设计 - 文章、博客文章、照片、故事

Question

我正在为一个网站设计一个数据库，该数据库将至少包含 4 种不同的对象类型（文章、博客文章、照片、故事），每个对象都有不同的足够数据要求来保证自己的表。我们希望用户能够针对任何这些类型发表评论。评论的数据要求很简单，并且与评论所关注的事物类型无关（即只是评论正文和作者的电子邮件）。

我希望避免为评论创建和管理 4 个以上单独表的冗余，因此我希望能够将所有评论保存在一个表中，可能通过 2 列指定关系：一列用于指定父实体，一列用于指定父实体为父行 ID。

但我不明白如何实现外键，因为外键在 2 个且仅 2 个表之间建立了关系（对吗？）。

考虑到所有这些，最好的方法是什么？

Answer 1

这是为您的应用程序实现超类型/子类型表的一种方法。

首先，超类型表。它包含所有子类型共有的所有列。

CREATE TABLE publications (
  pub_id INTEGER NOT NULL PRIMARY KEY,
  pub_type CHAR(1) CHECK (pub_type IN ('A', 'B', 'P', 'S')),
  pub_url VARCHAR(64) NOT NULL UNIQUE,
  CONSTRAINT publications_superkey UNIQUE (pub_id, pub_type)
);

接下来是几个子类型表。

CREATE TABLE articles (
  pub_id INTEGER NOT NULL,
  pub_type CHAR(1) DEFAULT 'A' CHECK (pub_type = 'A'),
  placeholder CHAR(1) NOT NULL, -- placeholder for other attributes of articles
  PRIMARY KEY (pub_id, pub_type),
  FOREIGN KEY (pub_id, pub_type) REFERENCES publications (pub_id, pub_type)
);

CREATE TABLE stories (
  pub_id INTEGER NOT NULL,
  pub_type CHAR(1) DEFAULT 'S' CHECK (pub_type = 'S'),
  placeholder CHAR(1) NOT NULL, -- placeholder for other attributes of stories
  PRIMARY KEY (pub_id, pub_type),
  FOREIGN KEY (pub_id, pub_type) REFERENCES publications (pub_id, pub_type)
);

这些子类型表中的 CHECK() 和 FOREIGN KEY 约束可防止行引用超类型中错误类型的行。它有效地在子类型之间划分 pub_id 值，保证任何给定的 pub_id 只能出现在一个且仅一个子类型表中。这就是为什么您需要对列对 {publications.pub_id,publications.pub_type} 使用 PRIMARY KEY 或 NOT NULL UNIQUE 约束。

评论表很简单。鉴于所有子类型都具有相同的结构，您可以引用超类型。

CREATE TABLE comments (
  pub_id INTEGER NOT NULL REFERENCES publications (pub_id),
  comment_timestamp TIMESTAMP NOT NULL DEFAULT now(),
  commenter_email VARCHAR(10) NOT NULL, -- Only allow people who have 
                                        -- really short email addresses
  comment_text VARCHAR(30) NOT NULL,    -- Keep 'em short!
  PRIMARY KEY (pub_id, comment_timestamp, commenter_email)
);

添加一点数据。

INSERT INTO publications VALUES
(1,'A', 'url 1 goes here'),
(2,'A', 'url 2 goes here'),
(3,'S', 'url 3 goes here');

INSERT INTO articles VALUES
(1,'A', 'A'),
(2,'A', 'B');

INSERT INTO stories VALUES
(3,'S', 'A');

INSERT INTO comments VALUES
(1, now(), '[email protected]','You''re stupid'),
(1, now(), '[email protected]', 'You''re stupid, too!');

现在您可以创建一个视图来显示所有文章并解析连接。您可以对每个子类型执行相同的操作。

CREATE VIEW articles_all AS
SELECT P.*, A.placeholder
FROM publications P
INNER JOIN articles A ON (A.pub_id = P.pub_id)

您可能更喜欢“published_articles”这样的名称，而不是“articles_all”。

要选择一篇文章及其所有评论，您只需左连接这两个表即可。 （但是请参阅下面为什么您可能不会这样做。）

SELECT A.*, C.*
FROM articles_all A
LEFT JOIN comments C ON (A.pub_id = C.pub_id)
WHERE A.pub_id = 1;

实际上您可能不会为 Web 界面执行此操作，因为 dbms 必须返回文章的“n”份副本，其中“n”等于文章的数量评论。但在某些应用中这样做确实有意义。在有意义的应用程序中，您将为每种子类型使用一个可更新视图，并且应用程序代码大多数时候都会使用可更新视图。

---

超类型/子类型更常见的业务应用涉及“各方”（超类型）、“组织”和“个人”（子类型，非正式的公司和人。地址与上例中的“注释”一样，与超类型相关，因为所有子类型（组织和个人）都有地址。

Answer 2

您可以在数据库设计中使用超类型/子类型来避免该问题。为图像、视频、注释创建一个超级类型，然后链接到该超级类型。将所有公共列保留在超类型表中。

以下是一些带有模型的类似问题/答案的链接：

• 一个
• 两个
• 三个
• 四个< /a>

Answer 3

在我看来，你最好有 4 个以上单独的表格来发表评论。或者你可以有连接表。一张表包含所有评论...例如：博客表、评论表、blog_comment 表。这将允许您拥有外键。

Blog
--------
Blog_id
{other fields}

Blog_Comment
--------------
Blog_id
Comment_id


Comment
------------
Comment_id
{other fields}