Earlier quoted context omitted.
What is annoying about implementing something like this in a relational database?
It's not straightforward to do polymorphic joins: one common pattern is to have child tables for each case of the union, but there's no integrity constraint such that each parent must only have one child, e.g. CREATE TABLE schools (id SERIAL PRIMARY KEY); CREATE TABLE colleges (id INTEGER NOT NULL REFERENCES schools (id)); CREATE TABLE high_schools (id INTEGER NOT NULL REFERENCES schools (id)); How can you ensure tha…
A combined approach works if want to encode the exclusive constraint:
CREATE TYPE school_type AS ENUM ('college', 'high_school');
CREATE TABLE schools (
id SERIAL PRIMARY KEY,
type school_type,
unique (id, type)
);
CREATE TABLE colleges (
id INTEGER NOT NULL,
type school_type default 'college',
check (type='college'),
foreign key (id, type) references school(id, type)
);
foreign key (id, 'college') references school(id, type)