8
$\begingroup$

The blog post states that a major drawback of SQL is its lack of sum type support.

Assuming we could redesign SQL today (without necessarily having to worry about compatibility) what are some specific features that could be added in formal support of sum types?

$\endgroup$
4
  • 1
    $\begingroup$ This question is rather broad. Asking “what would be the best approach” could mean lots of different things, and generally we are looking for more focused design questions here. Is there a specific aspect that you think would be challenging or difficult? $\endgroup$
    – Alexis King
    Aug 25, 2023 at 15:08
  • 1
    $\begingroup$ One complication to this question: the Cartesian product in a category of relations gives a biproduct, an object which is simultaneously a product type and a sum type. So, in a certain sense, SQL already has sums. Perhaps we can be more specific and ask about disjoint unions. $\endgroup$
    – Corbin
    Aug 26, 2023 at 16:44
  • 1
    $\begingroup$ @Corbin "the Cartesian product in a category of relations gives a biproduct" - The disjoint union of sets is a biproduct in Rel (<ncatlab.org/nlab/show/Rel#LimitsAndColimit>). The cartesian product of sets doesn't satisfy the coproduct universal property when we're in Rel (I can produce a counterexample if needed). So disjoint unions / sum types are important because without them Rel is not really a model of SQL. $\endgroup$ Jan 18 at 23:38
  • $\begingroup$ @lightandlight: You're entirely correct; where I said "Cartesian product" I should have said "disjoint union," making my comment half-wrong. Thanks for the correction. $\endgroup$
    – Corbin
    Jan 19 at 5:37

3 Answers 3

5
$\begingroup$

SQL has sum types in the sense that columns can be optionally populated.

A sum type is then represented by having a table with all the columns necessary for all the fields of all the variants of the sum type.

What's missing, though is formality:

  • a construct for grouping multiple columns into one variant of a sum type,
    • to be able to require columns of variant to be non-null when the row has that variant type,
    • to require one and only one variant's column group is populated
  • to name the sum type variant / column group
  • to identify the sum type variant of each row in the table

So, what I'd do is add named nested columns, a variant declaration for a table, a column type for expressing variant type, and query features to properly access the variants.

A variant declaration along with variant type declaration column, would ensure mutual exclusion between variants as is appropriate for sum types.

With this approach you can express a sum type in a single table.

Or, if you prefer, use foreign keys to other tables, where each foreign key to a different table for each variant is a variant in the main sum type table — but with mutual exclusion of variants feature and the other features from the list that I would add.

$\endgroup$
4
  • 1
    $\begingroup$ The approach in your last paragraph is the one given in the blog post. I think your first approach is more common, at least in simple "attribute-value" tables. They'll have columns like value_int, value_float, value_str, etc. $\endgroup$
    – Barmar
    Aug 28, 2023 at 17:34
  • $\begingroup$ @Barmar, the approach I describe in the last one is meant to use the mutual exclusion feature proposed here; I would not forgo the nested columns, a variant declaration for a table, a column type for expressing variant type, and query features to properly access the variants that I'm proposing, when using foreign keys. $\endgroup$
    – Erik Eidt
    Aug 28, 2023 at 19:14
  • $\begingroup$ The approach of using one column per summand only works if the sum only has primitive summands. In the general case you could have complex summands. Now you either need a reference to a table or multiple rows per summand. Support for primitive summands would still be progress, though. $\endgroup$ Aug 29, 2023 at 10:12
  • $\begingroup$ @ChristianLindig, yes, that's what nested columns would provide. $\endgroup$
    – Erik Eidt
    Aug 29, 2023 at 11:11
3
$\begingroup$

ActiveRecord from Ruby on Rails actually has a pretty good example of this. They call it polymorphic association.

The idea is this. A normal association (which is Rails' word for "foreign key") consists of a field called author_id whose integer value is a valid id in an authors table somewhere.

Now suppose we have a books table, but we want to split the authors out. Rather than having a single authors table, we want to have two: humans and machines. Either a human or a machine can write a book, so the books table should foreign key to one of those. With a polymorphic association, we define two fields in the books table.

  • author_type is the name (as a string) of the table we're linking to. In this example, it's either "humans" or "machines".
  • author_id is an integer which is a valid id in whatever table we're referencing.

Now, in Rails' case, all of this is enforced at the Ruby level. Rails just removes the foreign key constraint on polymorphic associations, so as far as the database is concerned, these are just ordinary string and integer fields. As far as I know, there's no SQL database software out there that supports this paradigm directly.

But I could imagine one that does. I could imagine a database where, in addition to FOREIGN KEY constraints, we have something called a POLYMORPHIC KEY constraint. This constraint applies to two fields on a table and demands, at the database level, that the _type string exist as a table (and optionally be one of a set of enumerated values, if we want to restrict which tables we can reference) and that the _id integer field exist as a valid id on that table. The constraint understands the key's linkage and can handle ON DELETE constraints, cascading, nulling, or protecting the source data as needed. JOINs are optimized for use cases that involve a string comparison followed by an id check, so we optimize for this case:

SELECT *
  FROM books b
  JOIN humans h ON b.author_type = 'human' AND b.author_id = h.id
$\endgroup$
1
  • $\begingroup$ You don’t need the column with a string for the table name, a foreign key constraint will contain the name of the table. You need a way to declare a conditional foreign key, which isn’t a problem because null is recognized as no FK restraint needed. So, how do you get the null, with a computed value of course. This will work today with SQL SERVER: computed persisted column, FK from books to humans. What is needed to keep it from being too tedious I would be to eliminate the persisted columns and allow FK based on inline calculations. $\endgroup$
    – jmoreno
    Sep 22, 2023 at 2:23
3
$\begingroup$

There are existing SQL implementations that do allow “sum types”. Notably, SQLite, with its dynamic typing system. But even otherwise statically-typed SQLs like Oracle (with its ANYDATA type) or Microsoft (with SQL_VARIANT) have ways of declaring that a column can contain mixed data types.

$\endgroup$
1
  • $\begingroup$ SQLite has something like an ANY type, but since any type can go in any column, it's more like strictly speaking every column is ANY type. $\endgroup$
    – qwr
    Dec 29, 2023 at 18:19

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .