5 ms·

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 { Col

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<Degree> }, 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 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.

jhgb 4y ago
I'm not sure that relational databases are "unable" to model something like this, since I recall from years ago Date describing how to do something like this. Don't remember the details, but you might want to look into Date's writings.
BeefWellington 4y ago
What is annoying about implementing something like this in a relational database?
- vore 4y ago
  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 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.
  - rq1 4y ago
    You add a xor non null check on the foreign keys?
  - roller 4y ago
    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) ); 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)
    - vore 4y ago
      Good point, I hadn't thought of that. Thanks!
  - go_prodev 4y ago
    > How can you ensure that a school is either a college or high_school but not both? 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.
    - vore 4y ago
      I'm modeling the GP's comment, but I think a common case is something like a polymorphic user/organization entity like GitHub: there is some kind of base user that is usable in a lot of places (e.g. https://github.com/<username> https://github.com/<username>) but there's also a load of distinct organization-specific fields and a load of distinct user-specific fields.
  - 4y ago
- AtlasBarfed 4y ago
  Auto-completion sucks in a lot of sql statements because the table provides all the hints that good autocompletion would need to provide good suggestions. 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.
  - andydd 4y ago
    Have you tried Datagrip?
  - layer8 4y ago
    That’s not necessarily a showstopper. Oracle’s SQL Developer editor for example provides useful completion in the SELECT clause if the FROM clause is already present.
srcreigh 4y ago
You'd have 1 table per sum type which requires extra data. Then polymorphic foreign key (aka a pair of fields school_type, school_id). (No foreign key constraints, but those are falling out of use in some cases due to inability to online migrate mysql schemas anyways.)
- ivank 4y ago
  You can retain foreign key constraints by having one column per type of reference. It is also possible to ensure that exactly one column of several is NOT NULL, so that the columns can always be mapped to an enum in application code. Also, in PostgreSQL, the storage for the extra NULLs uses just one bit per column in a bitmap. 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) );
- slaymaker1907 4y ago
  Yep, and you can also solve this problem by having a separate table for each variant and joining whenever you need to deal with common stuff. Personally, I think it's usually better just to have one giant table and allow the different columns to be null since it lets you avoid a bunch of verbose joins.
andyferris 4y ago
I totally agree with this. Interesting point about NoSQL! 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.
AlphaSite 4y ago
Doesn’t Postgres support this with table inheritance https://www.postgresql.org/docs/current/tutorial-inheritance.html https://www.postgresql.org/docs/current/tutorial-inheritance... I don’t know if they’re recommended, but they are an option.
- nickreese 4y ago
  Thank you for this. Modeling an annoying amount of similar models currently and this just gave me what I was looking for.
- ImprobableTruth 4y ago
  They're not recommended: https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_table_inheritance https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use.... They're incomplete (e.g. don't work with foreign keys) and are essentially unmaintained.
js8 4y ago
IMHO categorical data model (https://www.categoricaldata.net/ https://www.categoricaldata.net/) has much better support for sum types than relational model (as well as other advantages, roughly along the lines why to prefer type theory to set theory in math foundations), unfortunately practical databases and query languages are not yet well developed for it.

jhgb 4y ago

I'm not sure that relational databases are "unable" to model something like this, since I recall from years ago Date describing how to do something like this. Don't remember the details, but you might want to look into Date's writings.

BeefWellington 4y ago

What is annoying about implementing something like this in a relational database?

vore 4y ago
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 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.
- rq1 4y ago
  You add a xor non null check on the foreign keys?
- roller 4y ago
  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) ); 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)
  - vore 4y ago
    Good point, I hadn't thought of that. Thanks!
- go_prodev 4y ago
  > How can you ensure that a school is either a college or high_school but not both? 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.
  - vore 4y ago
    I'm modeling the GP's comment, but I think a common case is something like a polymorphic user/organization entity like GitHub: there is some kind of base user that is usable in a lot of places (e.g. https://github.com/<username> https://github.com/<username>) but there's also a load of distinct organization-specific fields and a load of distinct user-specific fields.
- 4y ago
AtlasBarfed 4y ago
Auto-completion sucks in a lot of sql statements because the table provides all the hints that good autocompletion would need to provide good suggestions. 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.
- andydd 4y ago
  Have you tried Datagrip?
- layer8 4y ago
  That’s not necessarily a showstopper. Oracle’s SQL Developer editor for example provides useful completion in the SELECT clause if the FROM clause is already present.

srcreigh 4y ago

You'd have 1 table per sum type which requires extra data. Then polymorphic foreign key (aka a pair of fields school_type, school_id). (No foreign key constraints, but those are falling out of use in some cases due to inability to online migrate mysql schemas anyways.)

ivank 4y ago
You can retain foreign key constraints by having one column per type of reference. It is also possible to ensure that exactly one column of several is NOT NULL, so that the columns can always be mapped to an enum in application code. Also, in PostgreSQL, the storage for the extra NULLs uses just one bit per column in a bitmap. 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) );
slaymaker1907 4y ago
Yep, and you can also solve this problem by having a separate table for each variant and joining whenever you need to deal with common stuff. Personally, I think it's usually better just to have one giant table and allow the different columns to be null since it lets you avoid a bunch of verbose joins.

andyferris 4y ago

I totally agree with this. Interesting point about NoSQL! 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.

AlphaSite 4y ago

Doesn’t Postgres support this with table inheritance https://www.postgresql.org/docs/current/tutorial-inheritance.html https://www.postgresql.org/docs/current/tutorial-inheritance... I don’t know if they’re recommended, but they are an option.

nickreese 4y ago
Thank you for this. Modeling an annoying amount of similar models currently and this just gave me what I was looking for.
ImprobableTruth 4y ago
They're not recommended: https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_table_inheritance https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use.... They're incomplete (e.g. don't work with foreign keys) and are essentially unmaintained.

js8 4y ago

IMHO categorical data model (https://www.categoricaldata.net/ https://www.categoricaldata.net/) has much better support for sum types than relational model (as well as other advantages, roughly along the lines why to prefer type theory to set theory in math foundations), unfortunately practical databases and query languages are not yet well developed for it.