数据库设计 - 文章，博客文章，照片，故事 [英] Database design - articles, blog posts, photos, stories

问题描述

我正在设计一个网站的数据库，该数据库将具有至少4种不同的对象类型（文章，博文，照片，故事），每个对象具有不同的数据要求，以保证自己的表格。我们希望用户能够发布任何这些类型的评论。意见的数据要求很简单，独立于评论所关注的事物的类型（即只是一个评论机构和作者的电子邮件）。

我想避免冗余创建和管理4个以上单独表格的注释，因此我希望能够在一个表中保留所有注释，可能通过2列指定关系：一个用于指定父实体，一个用于父行标识。

但是我不明白如何实现外键，因为外键建立2到2表之间的关系（对？）。

所以考虑到这一切，最好的方法是什么？

解决方案>

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

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

  CREATE TABLE出版物（
 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文章（
 pub_id INTEGER NOT NULL，
 pub_type CHAR（1）DEFAULT'A'CHECK（pub_type ='A'），
占位符CHAR（1）NOT NULL， - 文件的其他属性的占位符
 PRIMARY KEY（pub_id，pub_type），
 FOREIGN KEY（pub_id，pub_type）REFERENCES publication（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， - 其他故事属性的占位符
 PRIMARY KEY（pub_id，pub_type），
 FOREIGN KEY（pub_id，pub_type）REFERENCES出版物（pub_id，pub_type）
）; 
  

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

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

  CREATE TABLE注释（
 pub_id INTEGER NOT NULL参考出版物（pub_id），
 comment_timestamp TIMESTAMP NOT NULL DEFAULT now（），
 commenter_email VARCHAR（10）NOT NULL， - 只允许有
的人真的简短的电子邮件地址
 comment_text VARCHAR（30）NOT NULL， - 保持简短！
 PRIMARY KEY（pub_id，comment_timestamp，commenter_email）
）; 
  

添加一点数据。

  INSERT INTO出版物VALUES 
（1，'A'，'url 1 goes here'），
（2，'A'，'url 2 goes here ），
（3，'S'，'url 3去这里'）; 
 
 INSERT INTO文章VALUES 
（1，'A'，'A'），
（2，'A'，'B'）; 
 
 INSERT INTO故事VALUES 
（3，'S'，'A'）; 
 
 INSERT INTO评论VALUES 
（1，now（），'[e-mail]'，'你是愚蠢'），
（1，now（） ，'[e-mail]'，'你也是笨蛋！ 
  

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

  CREATE VIEW articles_all AS 
 SELECT P. *，A .placeholder 
 FROM出版物P 
 INNER JOIN文章A ON（A.pub_id = P.pub_id）
  

您可能会喜欢published_articles而不是articles_all这样的名称。

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

  SELECT A. *，C. * 
 FROM articles_all A 
 LEFT JOIN注释C ON（A.pub_id = C.pub_id）
 WHERE A.pub_id = 1; 
  

您可能不会为Web界面做到这一点，因为dbms必须返回'文章的副本，其中n等于评论的数量。但是在某些应用程序中这样做是有道理的。在有意义的应用程序中，您将为每个子类型使用一个可更新视图，应用程序代码将在大多数时候使用可更新视图。

---

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

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

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.

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

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

Add a little bit of data.

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(), '[e-mail]','You''re stupid'),
(1, now(), '[e-mail]', '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)

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;

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.

---

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.

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