A schema design walkthrough

What you will build#

This walkthrough connects three tables, inspects their diagram, and changes a referenced column using Rename. Editing and diagramming need no database account. Use a JCEF-enabled IDE runtime for DBML Diagram.

Create shop.dbml in an open project:

Project shop {
  database_type: 'PostgreSQL'
}

Table customers {
  id integer [pk, increment]
  email varchar(320) [not null, unique]
}

Table orders {
  id integer [pk, increment]
  customer_id integer [not null]
  total numeric(12,2) [not null, default: 0]
}

Table order_lines {
  order_id integer [not null]
  line_no integer [not null]
  quantity integer [not null, default: 1]
  indexes {
    (order_id, line_no) [pk]
  }
}

Ref: orders.customer_id > customers.id
Ref: order_lines.order_id > orders.id

Inspect the result#

Open View → Tool Windows → DBML Diagram. Expect three tables, a composite primary key on order_lines, and two relationships. Drag a table to separate crossing edges. Use Fit to recover the whole schema after zooming, and navigate back from a diagram table to its source.

Place the caret after customers. in the first Ref and invoke completion. id and email should be candidates from that table. Go to Declaration on customers.id should open its definition.

Make a schema change#

Rename customers.id to customer_id through Refactor → Rename. Review the usages, then apply. The relationship endpoint must change with the declaration. Undo restores both edits. This is the useful distinction between renaming a modeled column and replacing the same spelling throughout a file.

Continue with a multi-file project#

Download the modular shop example. Open main.dbml and compare the operations and identity views. Its intentionally invalid inspection playground is separate from the main schema. Use it for inspection exercises, not as a schema to export.

DBML source beside its live entity-relationship diagram. Select the image to view it at full size.