eCommerce database (DBML)

Updated
View as text
Open in Confluence
Open in ChatGPT Open in Claude

DBML reads like simplified DDL, which makes it easy to keep honest against the real schema. This is a complete small commerce schema.

#What this example shows

  • Enums, which constrain a column to a fixed set.

  • References with direction, which set the cardinality.

  • Composite indexes and table notes.

#A worked example

Open it, edit it, or copy the source below.

eCommerce database (DBML)

The source, which you can paste into a new diagram and edit:

Enum order_status {
  pending
  paid
  shipped
  delivered
  cancelled
}

Table customers {
  id uuid [pk]
  email varchar(255) [unique, not null]
  name varchar(255)
  tier varchar(32) [default: 'standard']
  created_at timestamptz [default: `now()`]

  Note: 'One row per person who can sign in'
}

Table orders {
  id uuid [pk]
  customer_id uuid [ref: > customers.id, not null]
  status order_status [not null, default: 'pending']
  total numeric(10,2) [not null]
  placed_at timestamptz

  Indexes {
    (customer_id, placed_at)
    status
  }
}

Table products {
  id uuid [pk]
  sku varchar(64) [unique, not null]
  name varchar(255) [not null]
  price numeric(10,2) [not null]
  active boolean [default: true]
}

Table order_items {
  id uuid [pk]
  order_id uuid [ref: > orders.id, not null]
  product_id uuid [ref: > products.id, not null]
  quantity int [not null, default: 1]
  unit_price numeric(10,2) [not null]

  Indexes {
    (order_id)
  }
}

#Making it your own

  • Reference direction matters: > is many-to-one, < one-to-many, - one-to-one.

  • Notes travel with the diagram, which beats a separate paragraph in a wiki.

  • Keep it next to the migrations so the two cannot drift apart.

Close enough to the schema to stay true.