Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Modelling algebraic data types using relational database

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
like image 380
Vladimir Keleshev Avatar asked Oct 19 '15 11:10

Vladimir Keleshev


People also ask

Which type of data is used in relational database?

A relational database is a collection of data items with pre-defined relationships between them. These items are organized as a set of tables with columns and rows. Tables are used to hold information about the objects to be represented in the database.

What is algebraic data type in Scala?

What Are Algebraic Data Types? ADTs are commonly used in Scala. Simply put, an algebraic data type is any data that uses the Product or Sum pattern. The question now shifts to understanding what Product and Sum Types are.

Why is it called algebraic data types?

Algebra of ADTs Product types and sum types are collectively called “algebraic” data types because they have algebraic properties similar to normal integers.

Does Java have algebraic data types?

Java is an object-oriented language, and doesn't support algebraic data types directly.


1 Answers

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

like image 90
solidsnack Avatar answered Oct 04 '22 22:10

solidsnack