I've thought about building a better query language too. I'd love the ability to model sum types in databases, something like: enum SchoolType { College { degrees: Vec }, HighSchool } It's such a common pattern and yet it's so annoying to model in a normal relational database. I wouldn't be surprised if the rise of NoSQL is tied to the inability of relational databases to model basic patterns like this. Part of me ha…
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));
Another alternative is to make one big table with check constraints but that's also hairy in its own right:
CREATE TYPE school_type AS ENUM ('college', 'high_school');
CREATE TABLE schools (
id SERIAL PRIMARY KEY,
type school_type,
/* college columns */,
/* high school columns */,
CHECK (type = 'college' AND /* college column constraints */),
CHECK (type = 'high_school' AND /* high school column constraints */)
);