Postgresql: foreign key for tableA or tableB

Question

I am trying to figure out how to determine the database schema using Postgresql 8.

I have 2 tables:

Journals, Books

which define the publications that I have

Journal:
id_j, name, issn, other fields

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

and I have another table Scansthat logically refers to both previous tables.

Scans:
id, medium, source, status

each Journalor Bookmay have more than one Scan, but each Scanmay only refer to one Journalor Book.

To formalize this, my first idea was to put two foreign keys in Scanshow

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

and fill in either id_jorid_b

but this decision seems a little strange to me.

I do not want (if possible) to define the table as follows:

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.