λ

SQL Database schemas

Write your schema yourself

DB-agnosticism

Use postgreSQL

Shameless plug

Avoid ORMs

But please use data mapping libraries

Pomm (PHP), anorm / slick (scala), …

Normal Forms

Design with Querying in mind

Primary Keys

Use UUIDs

(or random IDs)

Prevent entity enumeration

Prevent growth rate disclosure

Avoid linking the wrong table

Default to not null

Don't fear the join

Avoid deletions

Avoid deletions

created_at
    timestamp with time zone not null,
deleted_at
    timestamp with time zone

Singular table names

Uniform naming for PKs & FKs

<table_name>_id

Uniform naming for PKs & FKs

select <fields> from
  table_1
  inner join table_2
    on table_1.table_1_id =
       table_2.table_2_id

Uniform naming for PKs & FKs

select <fields> from
  table_1
  inner join table_2
    using (table_1_id)

Uniform naming for PKs & FKs

select <fields> from
  table_1
  natural join table_2

Use enums

create type status
as enum('pending', 'validated');

Use rich types

inet (IP address)
timestamp with time zone
point (2D point)
tstzrange (time range)
interval (duration)

Create your own

    create type my type
    as (
        field1 int,
        field2 text
    );

Use arrays

    select '{1,2,3}'::int[]

Rich types => powerful constraints

create table reservation(
    reservation_id uuid primary key,
    dates tstzrange not null,
    exclude using gist (dates with &&)
);

You can dump K/V data

You can dump JSON data

Common Table Expressions

with sub_request as (
    select <fields> from table1
)

select <fields> from sub_request;

Shameless plug

jDbT

https://github.com/divarvel/jdbt

jDbT

status:
  - Test
  - Prod

member:
  name: text
  email: text
  status: status | 'Test'
  __unique: [ name, email ]

jDbT

post:
  member_id:
  +?title: text
  ?content: text

tag:
    +name: text
    __check: name <> 'forbidden'

jDbT

post_tag:
    post_id:
    tag_id:
    __pk: [ tag_id, post_id ]

jDbT

Thanks