Rel database modelling conventions

Table and column names

If you are not using a pre-existing schema:

• Table names, keep them singular, lower and snake cased.
• Column names, keep them lower and snake cased.

Tables representation

Given a table named n with columns c₀, c₁, … define a module N (n capitalized) for it. This module should have

• An abstract type N.t for representing table rows.
• An N.row constructor for the row with arguments in the order of columns.
• (Optional) A user friendly N.v constructor with labelled arguments.
• Accessors N.c_i projecting the corresponding column c_i from N.t values.
• Values N.c'_i of type Rel.Col.t for each corresponding column c_i.
• A value N.table of type Rel.Table.t that defines the table.

Note that once you have modelled your tables you can use Rel.Sql.create_schema to output the corresponding schema in SQL's data definition language.

Example

Consider a person table which has three columns id and first_name and last_name columns. The following interface represents such a table according to the convention.

(** Persons. *)
module Person : sig

  type id = int
  (** The type for person identifiers. *)

  type t
  (** The type for persons. *)

  val v : id:id -> first_name:string -> last_name:string -> t
  (** [v ~id ~first_name ~last_name] is a person with given attributes.
      See accessors for semantics. *)

  val row : int -> string -> string -> t
  (** [row] is unlabelled {!v}. *)

  val id : t -> id
  (** [id p] is the unique identifier of [p]. *)

  val first_name : t -> string
  (** [first_name p] is the first name of [p]. *)

  val last_name : t -> string
  (** [last_name p] is the last name of [p]. *)

  (** {1:table Table} *)

  open Rel

  val id' : (t, id) Col.t
  (** [id'] is the {!id} column. *)

  val first_name' : (t, string) Col.t
  (** [first_name'] is the {!first_name} column. *)

  val last_name' : (t, string) Col.t
  (** [last_name'] is the {!last_name} column. *)

  val table : t Table.t
  (** [table] is the person table. *)
end

The simplest way of implementing this signature is by using OCaml records. For example:

module Person = struct
  type id = int
  type t = { id : id; first_name : string; last_name : string }

  let v ~id ~first_name ~last_name = { id; first_name; last_name }
  let row id first_name last_name = { id; first_name; last_name }

  let id r = r.id
  let first_name r = r.first_name
  let last_name r = r.last_name

  open Rel

  let id' = Col.v "id" ~params:[Col.Primary_key] Type.Int id
  let first_name' = Col.v "first_name" Type.Text first_name
  let last_name' = Col.v "last_name" Type.Text last_name

 let table =
   Table.v "person" Row.(unit row * id' * first_name' * last_name')
end