以下是一种实现应用程序超类型/子类型表格的方法。
首先,创建超类型表。它包含所有子类型共有的列。
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,
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,
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,
comment_text VARCHAR(30) NOT NULL,
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(), 'a@b.com','You''re stupid'),
(1, now(), 'b@c.com', 'You''re stupid, too!');
现在你可以创建一个视图来显示所有文章并解析join。 对于每个子类型,您将执行相同的操作。
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;
在网页界面中,你可能不会真的这样做,因为数据库管理系统需要返回与评论数相等的文章副本。但在某些应用程序中,这样做是有意义的。在适用的应用程序中,你将为每个子类型使用一个可更新视图,并且应用程序代码大部分时间都将使用可更新视图。
一个更常见的超类型/子类型的业务应用涉及“参与者”(超类型),“组织”和“个人”(子类型,非正式地称为公司和人员)。像上面例子中的“评论”一样,地址与超类型相关,因为所有子类型(组织和个人)都有地址。