12

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
Vladimir Keleshev
  • 13,753
  • 17
  • 64
  • 93
  • Could you please give an example of real data you might want to store? – Shnugo Oct 19 '15 at 11:29
  • 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... – Shnugo Oct 19 '15 at 11:33
  • @shnugo As I mention I'm interested in relational databases. – Vladimir Keleshev Nov 02 '15 at 15:05
  • 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... – Shnugo Nov 02 '15 at 15:09
  • https://blog.typeable.io/posts/2019-11-21-sql-sum-types.html – YisraelU Oct 26 '20 at 05:08

1 Answers1

10

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.)

solidsnack
  • 1,631
  • 16
  • 26