ER basics

An ER (entity-relationship) diagram shows the tables of a database: the columns each table holds, its keys, and how the tables relate to each other. Use one to design a new schema, to agree on one with your team before anyone writes a migration, or to explain an existing database to someone new.

Each element below comes with the schema code that draws it.

Tables and columnsCopy Anchor Link

A table is a card with its name at the top and one row per column. Each row shows the column's name on the left and its type on the right. A few marks tell you more about a column:

• A key icon, and a name in bold, means the column is part of the table's primary key.
• A link icon means the column points at another table: it's a foreign key.
• UQ next to the type means no two rows can have the same value.
• A ? after the type means the column can be empty. Columns without it always need a value.

Table customers {
  id integer [pk, increment]
  email varchar(255) [unique, not null]
  name varchar(120) [not null]
  phone varchar(30)
}

Name tables as plural nouns and columns in lower case with underscores, such as orders and customer_id. That's the style the assistant follows too.

KeysCopy Anchor Link

The primary key is the column, or set of columns, that picks out exactly one row. Most tables have an id column that the database numbers on its own. A table that joins two others, such as one that records which people belong to which teams, often uses both of its foreign keys together as its key instead.

Every table should have a primary key. The checks warn about a table that has none.

RelationshipsCopy Anchor Link

A relationship is a line from a column in one table to the key it points at in another table. The line's ends show how many rows can be on each side, in crow's foot notation:

• At the table that holds the reference, a crow's foot means many rows can point at the same row. A bar instead means only one can, as in a one-to-one relationship.
• At the table being pointed at, a bar means one. Next to it, a second bar means every row must have one, because the referencing column always needs a value. A circle means the reference can be empty.

There are three kinds:

Table customers {
  id integer [pk, increment]
  name varchar(120) [not null]
}

Table orders {
  id integer [pk, increment]
  customer_id integer [not null, ref: > customers.id]
  total decimal(10,2) [not null]
}

Select a relationship and the Properties panel says what it means in a sentence, such as "Each row in orders has exactly one row in customers; each row in customers has any number of rows in orders."

EnumsCopy Anchor Link

An enum is a type with a fixed list of values, such as the states an order goes through. It's drawn as a dashed card below the tables, marked enum, with one value per row. A column uses the enum by giving its name as the column's type.

Enum order_status {
  pending
  paid
  shipped
}

Table orders {
  id integer [pk, increment]
  status order_status [not null, default: 'pending']
}

Use an enum for a short list that rarely changes. When people edit the list, or each value carries data of its own, use a table instead.

Groups and notesCopy Anchor Link

A group is a frame around tables that belong to one area of the system, such as billing or the catalog. A table can be in one group at most. Groups help once a schema has more than about eight tables.

Table invoices {
  id integer [pk, increment]
  number varchar(30) [unique, not null]
}

Table invoice_lines {
  id integer [pk, increment]
  invoice_id integer [not null, ref: > invoices.id]
  amount decimal(12,2) [not null]
}

TableGroup billing {
  invoices
  invoice_lines
}

A note says what the names can't: what one row of a table is, what a column holds, or what the whole schema is for. A table with a note shows a note icon in its header; point at the icon to read it. Tables, columns, groups and the schema itself can each have a note.

When to use an ER diagramCopy Anchor Link

Each kind of diagram answers a different question:

• A BPMN process answers "what work happens, who does it, and what comes next".
• A sequence diagram answers "what do these systems say to each other, and in what order".
• An ER diagram answers "what do we store, and how does it fit together".

They work well side by side. A step in a process, such as "Record the order", writes rows to the tables an ER diagram shows, and a database in a sequence diagram holds them.

Keep each ER diagram to one system at one level of detail: about 3 to 25 tables. Leave out columns nobody has asked for yet, and group the tables by area once there are more than about eight.

Make an ER diagramCopy Anchor Link

• From the Diagrams panel. Select New diagram (the + button) and choose ER diagram.
• From the search menu. Press ⌘ K (Ctrl K on Windows and Linux) and run New ER diagram.
• From a template. Select Templates in the rail. Under ER diagram are Online store, Team workspace, Blog and Appointments. Selecting one creates a new diagram from it.
• From a file. In the Diagrams panel, open New diagram and choose Import SQL or DBML file…. See Import a file.

A new diagram is called "Untitled schema". Its empty canvas offers three ways to begin: Describe it opens the assistant, Add table adds the first table, and Use a template opens the Templates panel. You can also type the schema in the Schema code pane beside the canvas.

Work on the canvasCopy Anchor Link

The app lays out every ER diagram for you: it places the tables, keeps each group's tables together, puts the enums below, and routes each line so it ends on its column's row. You change what's there, and the layout follows. Where you want a table somewhere else, drag it there and it stays. Every edit is a small, named change that shows up in the version history and can be undone.

Move aroundCopy Anchor Link

• Pan: drag an empty part of the canvas, or scroll. To pan from anywhere, hold Space and drag, or drag with the middle mouse button.
• Zoom: pinch on a trackpad, or hold ⌘ (Ctrl on Windows and Linux) and scroll. + and - zoom too, and ⌘ 0 fits the diagram to the view.

SelectCopy Anchor Link

Click a table's header to select the table, or one of its rows to select that column. Click a line to select the relationship, or an enum or a group's frame to select it. Shift-click adds an element to the selection or takes it out (on a Mac, ⌘-click works too). ⌘ A selects every table, relationship, enum and group. To select an area, Shift-drag across empty canvas: the tables and enums it touches are added. Clicking empty canvas, or pressing Esc, clears the selection.

Selecting a table or a column darkens its lines and tints the columns at their other ends, so you can see what it's connected to. Selecting an enum tints the columns that use it.

The arrow keys move the selection: ↑ and ↓ go through each table and its columns in reading order, then the enums and groups, and ← and → go from table to table.

Add elementsCopy Anchor Link

The palette along the bottom of the canvas has Table, Enum and Group:

• A table goes after the selected table and into its group, into the selected group, or at the end. It starts with an id integer key.
• An enum starts with two values, active and archived, to rename in Properties.
• A group goes around the selected tables. With no table selected, it starts empty.

To add a column, select a table and choose Add column in the selection bar. The column goes at the end of the table, or below the selected column. To add a value to an enum, select it and choose Add value.

The new element opens for naming straight away; type and press Enter.

From the keyboard, press T table, C column, E enum or G group. With a column selected, K makes it part of the primary key, or takes it out.

Right-clicking empty canvas offers Add table, Add enum and Add group too.

Relate two columnsCopy Anchor Link

Drag from a column's row to a column in another table. While you drag, a line follows the pointer and a caption says what dropping will do, such as "Relate to customers.id". Let go to add the relationship.

If one of the two columns is a key or unique, it's the one being pointed at. Otherwise the column you dragged from holds the reference. A new relationship is many to one; change that in Properties. If the two columns are already related, the caption says so and nothing is added.

To relate two columns of the same table, such as a comment's parent_id to its id, hold ⌥ (Alt on Windows and Linux) while you drag. On a touch screen, dragging pans instead.

Move columnsCopy Anchor Link

Columns keep the order you give them. Hold ⌥ (Alt) and press ↑ or ↓ to move the selected column, choose Move up or Move down in the selection bar, or drag the row up or down within its table.

Move tablesCopy Anchor Link

Drag a table by its header, an enum, or a group by its frame, and let go where you want it. It lands on the grid, the lines that touch it follow while you drag, and a group's frame grows to hold its tables. Drag one of several selected tables to move them all. Near the edge of the canvas, the view pans so you can keep going.

A table you've moved stays where you put it when you edit the diagram, in the code or with the assistant. Tables you haven't moved are still placed by the layout. Schema code doesn't write where tables are, so a file you export or a schema you import carries no positions. To put everything back where the layout places it, choose Tidy up in the menu on empty canvas.

Use the selection barCopy Anchor Link

When you select an element, a bar of tools floats above it. What it starts with depends on the element:

Every element then has Ask assistant, Comment, Rename (not for relationships), Properties and Delete. For a group, Delete is Ungroup: the frame goes and its tables stay. With several elements selected, the bar offers Group tables (when the selection has a table), Ask assistant, Comment and Delete.

Flip direction swaps which side of a relationship holds the reference. Require a value and Allow empty are off for a key column, which always needs a value.

Right-click an element, long-press it on a touch screen, or press ⇧ F10 for a menu with the same tools. A table's menu also has Add table and Group table.

RenameCopy Anchor Link

Double-click a table, column, enum or group, or select it and press Enter or F2, to rename it in place. Press Enter to save or Esc to cancel. Relationships point at columns, not names, so renaming a table or a column changes nothing else.

A column's type, an enum's values and everything else are edited in Properties. Double-clicking a relationship, or pressing Enter on it, opens it there.

Delete, comment and undoCopy Anchor Link

• Delete: select elements and press ⌫ (Backspace) or Delete. Deleting a table or a column also deletes its relationships. A table needs at least one column, so to remove the last one, delete the table. Deleting an enum turns the columns of its type into varchar.
• Comment: select elements and press M. Comments work as they do on a process. See Comment on the diagram.
• Ask the assistant: select elements and press ⌘ L (Ctrl L). The assistant takes "this" or "these" to mean them.
• Undo: ⌘ Z (Ctrl Z) undoes, and ⇧ ⌘ Z (Shift Ctrl Z) redoes. See Undo and redo.

See and change every propertyCopy Anchor Link

The Properties panel shows every field the diagram stores for what you've selected. Open it with Properties in the selection bar, or ⌥ ⌘ P (Alt Ctrl P). Each change is one undo step.

Default takes a value as the code writes it: 0, true, 'draft' in single quotes, or an expression in backticks, such as `now()`. Setting Reference to Required makes the referencing columns Not null.

Each element shows its id at the bottom, with Copy.

Check your diagramCopy Anchor Link

The Problems panel checks an ER diagram every time it changes, as it does a process. See Modeling checks for how the panel works.

• Errors: a relationship refers to a table or column that isn't in the diagram, or its two ends have different numbers of columns.
• Warnings: a table has no primary key; a relationship is many to many (add a junction table); a foreign key's type doesn't match the key it points at, such as integer pointing at uuid; a relationship points at a column that isn't a primary key or unique; or a one-to-one relationship starts from a column that isn't unique.
• To finish: a table isn't related to any other table, no column uses an enum, or a group has no tables.

Types that differ only in spelling or size don't count as a mismatch: int pointing at integer, or varchar(100) at text, is fine.

ExportCopy Anchor Link

Open Export at the top right of the diagram. Pictures are under Image, text files under Code:

View PostgreSQL SQL…, View MySQL SQL…, View SQL Server SQL… and View Mermaid… at the bottom of the menu show the text read-only, with a button to copy it.

Each SQL script creates the tables with their keys, defaults and indexes, and adds the foreign keys last, so it runs whatever order the tables are in. A few common types are written the way each database spells them: uuid becomes char(36) in MySQL and uniqueidentifier in SQL Server, for example. Enums become a type of their own in PostgreSQL, an ENUM column in MySQL, and a text column with a check in SQL Server. A many-to-many relationship has no foreign key to write, so it becomes a comment saying a junction table is needed.

The Mermaid file has the tables, their columns and keys, and the relationships. Enums, groups, indexes and notes are left out.

NextCopy Anchor Link

Learn to write schema code, or let the assistant design a schema for you.