Modelling algebraic data types using relational database

Question

Say you are writing an app in OCaml/F#/SML/Haskell and want to persist data in a relational database. It is easy to map product types (records and tuples) to relations, but how do you map variant types to relations?

To be concrete, how would you persist a type like the following, in a relational database?

(* OCaml *)
type t = 
  | Foo
  | Bar of string
  | Baz of int * int * int

Felike

Could you please give an example of real data you might want to store? – Andantino

My first idea would be XML. You can store any kind of hierachically structured data together with descriptive meta data (attributes). But you'll need high logic to interpret this... – Andantino

@shnugo As I mention I'm interested in relational databases. – Felike

Hi, you did not mention, which RDBMS you'd like to use. I'm using SQL Server. There are incredible possibilities to use XML within the relatioal db schema. Just define an XML column as part of a table and place there literally any data you want. Doing so, you can combine the strengthes of a RDBMS with the flexibility of XML. This was just a thought... – Andantino

blog.typeable.io/posts/2019-11-21-sql-sum-types.html – Selfpossessed

Answer 1

It seems tedious but I would create a table for each product in the sum.

CREATE TABLE foo (id uuid PRIMARY KEY);

CREATE TABLE bar (id uuid PRIMARY KEY,
                  s  text NOT NULL);

CREATE TABLE baz (id uuid PRIMARY KEY,
                  a  integer NOT NULL,
                  b  integer NOT NULL,
                  c  integer NOT NULL);

You probably want to store some metadata along with records of each type:

CREATE TABLE envelope (id uuid PRIMARY KEY,
                       t  timestamptz NOT NULL DEFAULT now(),
                       by text NOT NULL DEFAULT sessions_user);

And this suggests a foreign key constraint:

CREATE TABLE foo (id uuid PRIMARY KEY REFERENCES envelope);

CREATE TABLE bar (id uuid PRIMARY KEY REFERENCES envelope,
                  s  text NOT NULL);

CREATE TABLE baz (id uuid PRIMARY KEY REFERENCES envelope,
                  a  integer NOT NULL,
                  b  integer NOT NULL,
                  c  integer NOT NULL);

And if you are even stricter you could imagine storing a ty column with the name of the type and using it to construct a composite foreign key. (As described under "Where Not to Use Table Inheritance" in the LedgerSMB blog.)

Thankless

I have lately thought more about this and have a scheme involving an intermediate table to serve as a foreign key target. Some example code can be found here: github.com/solidsnack/pg-sql-variants – Thankless

How would I store a reference to a t? – Firenze

Store a reference to the envelope. – Thankless

Was anything edited? I can't understand my own question ;) – Firenze