Modeling Algebraic Data Types Using a Relational Database

Question

Say you are writing an application in OCaml / F # / SML / Haskell and want to save the data in a relational database. It's easy to match product types (records and tuples) with relationships, but how do you compare type options with relationships?

To be specific, how would you save a type like the following in a relational database?

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

Answer 1

It seems tedious, but I would create a table for each product in total.

 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 save some metadata along with each type of record:

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

And this involves limiting the foreign key:

 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 more strict, you can imagine the storage of the ty column with the type name and its use to build a complex foreign key. (As described in “Where Not to Use Table Inheritance” on the LedgerSMB blog.)