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

问题描述

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

I'm designing a database for a web site that will have at least 4 different object types represented (articles, blog posts, photos, stories), each of which have different enough data requirements to warrant their own tables. We want users to be able to post comments for any of these types. The data requirements for comments are simple and independent of the type of thing the comment regards (ie just a comment body, and the author's email).

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

I want to avoid the redundancy of creating and managing 4+ separate tables for the comments, so I'd like to be able to hold all comments in one table, possibly specifying the relation via 2 columns: one to designate the parent entity and one for the parent row Id.

但我不明白我将如何实现外键，因为外键在 2 个表和仅 2 个表之间建立关系(对吗?).

but I don't understand how, then, I would implement foreign keys, since foreign keys establish a relation between 2 and only 2 tables (right?).

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

So with all that in mind, what would be the best approach?

推荐答案

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

Here's one way to implement supertype/subtype tables for your app.

首先是超类型表.它包含所有子类型共有的所有列.

First, the supertype table. It contains all the columns common to all subtypes.

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

接下来是几个子类型表.

Next, a couple of subtype tables.

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 约束.

The CHECK() and FOREIGN KEY constraints in these subtype tables prevent rows from referencing the wrong kind of row in the supertype. It effectively partitions the pub_id values among the subtypes, guaranteeing that any given pub_id can appear in one and only one of the subtype tables. That's why you need either a PRIMARY KEY or NOT NULL UNIQUE constraint on the pair of columns {publications.pub_id, publications.pub_type}.

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

The table for comments is simple. Given that it is to have the same structure for all subtypes, you can reference the supertype.

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

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

Now you can create a view to show all articles and resolve the join. You'd do the same for each of the subtypes.

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".

You might prefer names like "published_articles" instead of "articles_all".

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

To select one article and all its comments, you can just left join the two tables. (But see below why you probably won't do that.)

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"等于评论数.但在某些应用程序中这样做确实有意义.在有意义的应用程序中，您会为每个子类型使用一个可更新视图，而应用程序代码大部分时间将使用可更新视图.

You'd probably not actually do that for a web interface, because the dbms would have to return 'n' copies of the article, where 'n' equals the number of comments. But it does make sense to do this in some applications. In applications where it makes sense, you'd use one updatable view for each subtype, and application code would use the updatable views most of the time.

超类型/子类型更常见的业务应用涉及Parties"(超类型)、Organizations"和Individuals"(子类型，非正式的公司和人>. 地址，如上例中的评论"，都与超类型相关，因为所有的子类型(组织和个人)都有地址.

The more common business application of a supertype/subtype involves "Parties" (the supertype), "Organizations" and "Individuals" (the subtypes, informally companies and people. Addresses, like "comments" in the example above, are related to the supertype, because all the subtypes (organizations and individuals) have addresses.

这篇关于数据库设计 - 文章、博客文章、照片、故事的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！