Inventory management (DBML)

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

An inventory schema is a good DBML example because the interesting part is the movement table, which is where most of the rules live.

#What this example shows

  • A movement table with a signed quantity rather than separate in and out tables.

  • Composite unique constraints across two columns.

  • A self-referencing hierarchy on locations.

#A worked example

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

Inventory management (DBML)

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

Table locations {
  id uuid [pk]
  code varchar(32) [unique, not null]
  name varchar(255)
  parent_id uuid [ref: > locations.id, note: 'Null for a top-level site']
  kind varchar(32) [note: 'site, zone, aisle, bin']
}

Table items {
  id uuid [pk]
  sku varchar(64) [unique, not null]
  name varchar(255) [not null]
  unit varchar(16) [default: 'each']
  reorder_level int [default: 0]
}

Table stock_levels {
  id uuid [pk]
  item_id uuid [ref: > items.id, not null]
  location_id uuid [ref: > locations.id, not null]
  quantity int [not null, default: 0]

  Indexes {
    (item_id, location_id) [unique]
  }
  Note: 'Derived from movements; kept for fast reads'
}

Table movements {
  id uuid [pk]
  item_id uuid [ref: > items.id, not null]
  from_location_id uuid [ref: > locations.id]
  to_location_id uuid [ref: > locations.id]
  quantity int [not null, note: 'Always positive; direction comes from the locations']
  reason varchar(64) [note: 'receipt, transfer, pick, adjustment, writeoff']
  occurred_at timestamptz [not null, default: `now()`]

  Indexes {
    (item_id, occurred_at)
  }
}

#Making it your own

  • A self-reference (parent_id to the same table) is how you draw a hierarchy in DBML.

  • Column notes are the cheapest documentation you will ever write.

  • Mark derived tables as derived, or somebody will write to them directly.

Close enough to the schema to stay true.