They're incomplete (e.g. don't work with foreign keys) and are essentially unmaintained.
I'm not sure if it's just the query language though - the definition language needs to make creating columns that are sum types trivial. For one-to-many data this might be a slight generalization of foreign key (compound of table tag + foreign key for that table). This can work for one-to-one data too, but can be a bit annoying having lots of tables compared to doing adding a couple nullable columns (plus there's also data locality differences). I suppose a wrapper language that covers both DDL and DML could work.
(No foreign key constraints, but those are falling out of use in some cases due to inability to online migrate mysql schemas anyways.)
CREATE TABLE dirents (
parent bigint NOT NULL,
child_dir bigint,
child_file bigint,
child_symlink bigint,
basename text NOT NULL,
-- Ensure exactly one type of child is set
CHECK (num_nonnulls(child_dir, child_file, child_symlink) = 1),
CONSTRAINT dirents_child_dir_fkey FOREIGN KEY (child_dir) REFERENCES dirs (id),
CONSTRAINT dirents_child_file_fkey FOREIGN KEY (child_file) REFERENCES files (id),
CONSTRAINT dirents_child_symlink_fkey FOREIGN KEY (child_symlink) REFERENCES symlinks (id),
PRIMARY KEY (parent, basename)
); 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 that a school is either a college or high_school but not both?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 */)
);
The other thing in the grandparent's comment that's a constant pain in SQL is representing an ordered list: how do you insert items into the middle of the list? Depending on your database, it can also be painful to renumber the other items. 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)
);
Ya, the syntax is annoying and repetitive. It would be nice if foreign key could be a literal to remove the extra column altogether. e.g.: foreign key (id, 'college') references school(id, type)The latter makes little sense. What constraints are you placing on a college that do not also apply to a highschool, given they're both schools?
> The other thing in the grandparent's comment that's a constant pain in SQL is representing an ordered list: how do you insert items into the middle of the list? Depending on your database, it can also be painful to renumber the other items.
I'm unclear on what you mean by this. If you want a list of records ordered in a certain fashion, there's an entire "ORDER BY" clause for that express purpose. If you're trying to add "extra" data into the middle of some list that is not otherwise represented in the data in the database, that's essentially business logic and you should be using some kind of custom view or procedure to do that or doing it inside your application code.
If it's just a question of how you add data into the middle of a resultset from actual data in a table based on some arbitrary ordering, you can do that too, people solved that problem ages ago by simply having an ORDER column or similar that's just an int with whatever likeliest precision you get, e.g.: default might be 1000 and then if need be you can insert 999 items between two others before needing to do a re-numbering on the column. These are dumb tricks but needing to "insert a record between two other records" is often also a dumb trick someone is trying to do in the database because they haven't designed things well elsewhere.
I'd venture the 99.9% case is querying real live data in the database and ordering it by factual things like record names, dates of update or creation, status, etc.
EDIT: To expand further, I would generally model a playlist as its own tables anyways. Something like:
CREATE TABLE playlist (
id ID_TYPE_FOR_DB PRIMARY KEY,
name varchar(4096), /* Or whatever storage size makes sense */
)
CREATE TABLE playlist_entry (
id ID_TYPE_FOR_DB PRIMARY KEY,
song_ref ID_TYPE_FOR_DB FOREIGN KEY REFERENCES songs(id),
order INT, /* Or bigint or whatever you want */
)Do you have any real world scenarios where you've faced this problem?
In your example, you wouldn't model it like that. A school just needs an attribute that identifies the type of school (high school or college), and other attributes that would be common to both.
I'm sure there's lots of examples but it's late and I'm struggling to think of one that a good normalized data model couldn't handle.
If the DBMS supports it, you can add check constraints that query the other tables. See for example here: https://stackoverflow.com/a/2588427
That sounds like a nitpick, but man is it useful when you need it.
Notice how the first thing in PRQL is the table declaration.
The fact that UPDATE and INSERT have different syntaxes for basically specifying the same mutation operation is pretty dumb.
Part of me has wondered if a language is the solution. Maybe just a better query builder with support for sum types is necessary. But I suppose there's something useful about having a consistent model based around a language, even if people aren't writing the language directly.