postgresql: foreign key to either tableA or tableB

Question

I'm trying to figure out how to define the schema of a database on with Postgresql 8.

I have 2 tables:

Journals, Books

that define the publications I have

Journal:
id_j, name, issn, other fields

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

and I have another table Scans that logically refers both the previous tables.

Scans:
id, medium, source, status

each Journal or Book can have more than one Scan, but each Scan can refer only one Journal or Book.

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

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

and fill in either id_j or id_b

but this solution seems to me a bit weird.

I don't want (if it is possible) to define the table in a way like this:

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

because I would like to have a formal connection between the tables.

Any idea?

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

With this design, you should not delete records directly from book or journal: instead, delete from table source which will cascade the operation to the appropriate table.

You can move the attributes that are common to book and journal to source.