sql - postgresql:tableA 或 tableB 的外键

Question

我正在尝试弄清楚如何使用 Postgresql 8 定义数据库的模式。

我有 2 个表:

期刊，书籍

定义我拥有的出版物

Journal:
id_j, name, issn, other fields

Book:
id_b, name, isbn, author, other fields

我还有另一个表Scans，它在逻辑上引用了前面两个表。

Scans:
id, medium, source, status

每个Journal或Book可以有多个Scan，但是每个Scan只能引用一个Journal 或 Book。

为了形式化，我的第一个想法是在 Scans 中放置两个外键，例如

Scans:
id, medium, source, status, id_j, id_b

并填写id_j或id_b

但这个解决方案在我看来有点奇怪。

我不想(如果可能的话)以这样的方式定义表格:

Scans:
id, medium, source, status, id_other_table, other_table_name

因为我希望表之间有一个正式的联系。

有什么想法吗？

Answer 1

CREATE TABLE source (
       type CHAR(1) NOT NULL CHECK (type IN ('J', 'B')),
       id INT NOT NULL,
       PRIMARY KEY (type, id)
);

CREATE TABLE book (
       type CHAR(1) NOT NULL CHECK(type = 'B'), id INT NOT NULL,
       PRIMARY KEY (id),
       FOREIGN KEY (type, id) REFERENCES source (type, id) ON DELETE CASCADE
);

CREATE TABLE journal (
       type CHAR(1) NOT NULL CHECK(type = 'J'), id INT NOT NULL,
       PRIMARY KEY (id),
       FOREIGN KEY (type, id) REFERENCES source (type, id) ON DELETE CASCADE
);

CREATE TABLE scan (id INT NOT NULL, sourcetype CHAR(1) NOT NULL, sourceid INT NOT NULL,
       PRIMARY KEY (id),
       FOREIGN KEY (sourcetype, sourceid) REFERENCES source (type, id)
);

使用这种设计，您不应该直接从 book 或 journal 中删除记录:相反，从表 source 中删除，这将级联操作到适当的表。

您可以将book 和journal 共有的属性移动到source。