ERD (Entity-Relationship Diagram)

About ERDs

An Entity-Relationship Diagram (ERD) documents the structure of a relational database: tables (entities), their columns (attributes), and the foreign-key relationships between them — including cardinality (1..1 / 0..1 / 1..N / 0..N). Backend engineers draw them to onboard new teammates onto a codebase. Data architects sketch them on a whiteboard before writing any DDL. Database educators (Elmasri & Navathe; Silberschatz / Korth / Sudarshan) put them at the centre of the conceptual-design unit in every CS database course.

Schematex implements crow's-foot notation — the de-facto modern style used by MySQL Workbench, dbdiagram.io, ERDPlus, Lucidchart, draw.io, Mermaid erDiagram, and Oracle SQL Developer Data Modeler. The DSL is DBML-compatible-ish so engineers can copy-paste; it also accepts the Mermaid }o--|| ASCII glyphs as input aliases for users coming from Mermaid. The Chen 1976 notation (rectangles + diamonds + ovals + ISA hierarchies) and Barker overlay are deferred to v0.2; today, every diagram is rendered as crow's foot.

Note — This is not the same as the entity engine. entity (entity structure) is for corporate / legal ownership hierarchies (subsidiaries, percentage rollup, trusts). erd is for database schemas (tables, columns, foreign keys). Different domain, different layout, different audience.

1. Your first ERD

The smallest useful ERD: a parent table referenced by a child via foreign key.

Four rules cover 80 % of usage:

1. Start with erd. Optional headers title:, direction: LR | TB.
2. Each table is a block: table Name { col type marker }. Markers are PK, FK, UK, NN (or *) for NOT NULL.
3. Inline foreign-key target: append FK -> Other.column to a column. The renderer adds an FK pill automatically.
4. Connect tables with a ref line: ref Source <left-card> -- <right-card> Target [: "label"]. Cardinality is one of one-mandatory, one-optional, many-mandatory, many-optional (named form), 1..1 / 0..1 / 1..N / 0..N (Min-Max), or Mermaid glyphs (}o--||, etc.) as alias.

Comments use // or #. Block forms can be either multi-line (one column per line) or inline (table A { id int PK; name varchar }).

2. Cardinality glyphs

Crow's foot encodes the cardinality at each end of the relationship line:

Each end of a line is annotated independently. A typical 1:N relationship reads "exactly one CUSTOMER places zero-or-more ORDERs":

ref Order.customer_id many-mandatory -- one-mandatory Customer.customer_id : "places"

Reading right-to-left: the right end of the line attaches to Customer with a single bar (one-mandatory); the left end attaches to Order with a bar + crow's foot (many-mandatory).

3. Mermaid alias

If you already use Mermaid erDiagram, the same glyphs work as input:

4. Identifying vs non-identifying

Use -- for identifying relationships (solid line) and .. for non-identifying (dashed):

The dashed line follows the Barker / IDEF1X non-identifying convention.

5. Composite primary keys (associative tables)

Associative ("junction") tables resolve M:N relationships. Mark each PK + FK column with both PK and FK:

Schematex renders both pills (PK + FK) on the same row. The PK underline applies to the column name when any PK marker is present.

6. Layout direction

direction: LR (default) places parent tables left, child tables right. direction: TB flips to top-to-bottom — useful for narrow embeds:

Layered orthogonal Manhattan routing with single-bend edges. v0.1 uses appearance-order stacking inside layers; barycenter crossing-reduction is deferred to v0.2.

7. Notation modes

The DSL header can pin the notation:

erd
notation: crowsfoot     // default — only mode supported in v0.1

notation: chen (rectangle-diamond-oval, weak entities, ternary, ISA) and notation: barker (per-half dashed) are documented in 27-ERD-STANDARD.md but rejected by the parser today. Targets v0.2.

8. Limitations of v0.1

• Crow's foot only. Chen and Barker rendering deferred.
• No edge-crossing minimization. Layered placement uses declaration order within layers; large ERDs (10+ tables, dense FKs) will show some crossings. Reorder table blocks if a specific layout matters.
• No self-referential C-loops. Recursive relationships (Employee.manager_id -> Employee.id) parse but route as straight orthogonal lines, not the canonical C-shape.
• No M:N auto-resolution. Express the associative entity explicitly (this is the standard practice in production schemas anyway).

For the full standard reference, see docs/reference/27-ERD-STANDARD.md.