ER Diagram (Mermaid)

ER diagrams: from Chen's 1976 paper to modern crow's-foot notation

An Entity-Relationship (ER) diagram is the lingua franca of conceptual data modeling. Introduced by Peter Chen in his seminal 1976 paper "The Entity-Relationship Model — Toward a Unified View of Data", it gave database designers a visual vocabulary to describe what information a system stores and how the parts relate, independent of any particular DBMS or physical layout. Fifty years later, ER diagrams still drive every serious schema design discussion — from a whiteboard sketch on day one to the diagrams generated automatically from Prisma, dbdiagram.io, or MySQL Workbench.

This generator emits a clean ER outline you can paste into Mermaid, dbdiagram.io DBML, or a slide deck. Below is the long-form reference: the original Chen symbols, the more popular crow's-foot variant, cardinality rules, normalization, weak vs strong entities, ISA hierarchies, and how to keep the diagram synchronized with the live database.

Chen's original components

• Entity — a thing the business cares about, drawn as a rectangle. Examples: User, Order, Product, Invoice.
• Attribute — a property of an entity, drawn as an oval connected to the rectangle. Examples: name, email, price.
• Relationship — a verb connecting entities, drawn as a diamond. Examples: places (User-Order), contains (Order-LineItem).
• Identifier / Key — the attribute (or set of attributes) that uniquely identifies a row, drawn with an underline.
• Derived attribute (dashed oval), multivalued (double oval), composite (oval with sub-ovals).

Crow's-foot notation: the de facto modern style

Most contemporary tools (dbdiagram.io, drawSQL, Lucidchart, MySQL Workbench, ERWin) use the crow's-foot variant. Each entity becomes a rectangle that lists its attributes inline, and the line between two entities ends with symbols describing the cardinality at each end:

• | — exactly one (mandatory).
• o — zero (optional).
• >— or —< — the "crow's foot": many.
• Combine them: |o = zero or one, || = exactly one, }o = zero or many, }| = one or many.

Mermaid's erDiagram block uses ASCII versions of these symbols:

erDiagram
  CUSTOMER ||--o{ ORDER : places
  ORDER    ||--|{ LINE-ITEM : contains
  ORDER    }o--|| ADDRESS : ships-to
  CUSTOMER {
    int    id PK
    string name
    string email "unique"
  }

Cardinality: 1:1, 1:N, and N:N

• 1:1 — rare. Often a sign that two entities should be merged, or that you are vertically partitioning a wide table (e.g. User and UserProfile).
• 1:N — the most common relationship. A Customer places many Orders; an Order belongs to exactly one Customer. Implemented with a foreign key on the "many" side.
• N:N — resolved by a join table (also called bridge or associative entity). Student × Course becomes Enrollment, with FKs to both and optional attributes (grade, date) of its own.

Strong vs weak entities and ISA hierarchies

A strong entity owns a primary identifier (Order.id). A weak entity cannot be identified by its own attributes alone and depends on an "owner" entity — for example OrderLine with (order_id, line_no) as composite key. In crow's-foot drawings a weak entity has a double border and the identifying relationship is drawn with a double diamond.

ISA / generalization / specialization models inheritance: Person can be specialized into Student and Professor, sharing common attributes but adding their own. This becomes class table inheritance, single table inheritance, or joined inheritance once you implement it physically — the conceptual diagram leaves that choice for later.

Normalization: from 1NF to BCNF

Once the ER diagram exists, normalization tells you whether the resulting tables avoid anomalies:

• 1NF — every column holds atomic values, no repeating groups, no arrays-in-columns.
• 2NF — every non-key column depends on the whole primary key (relevant for composite keys).
• 3NF — no transitive dependencies (a non-key column depending on another non-key column).
• BCNF — stricter 3NF where every determinant is a candidate key.
• 4NF / 5NF — remove multivalued and join dependencies; rarely needed in OLTP.

Conceptual, logical, physical

Most teams iterate through three levels of fidelity: conceptual (entity names + relationships, no datatypes), logical (attributes with types, keys, normalization applied, still DB-agnostic) and physical (PostgreSQL VARCHAR(255), MySQL BIGINT UNSIGNED, indexes, partitions, materialized views). Use the conceptual diagram to align with product, the logical diagram for engineers, and the physical model for DBAs and DevOps.

FAQ

ER diagram vs UML class diagram — what's the difference? UML class diagrams describe object-oriented designs (methods, inheritance, visibility); ER diagrams describe relational data (entities, attributes, cardinality). They overlap heavily but speak to different audiences — backend engineers reach for UML, DBAs for ER.

How much detail should an ER diagram show? Start conceptual (boxes and verbs only) to align stakeholders, then enrich it with attributes and types only when the schema is stable. Putting every nullable column on the day-one diagram makes it unreadable.

How do I keep the diagram in sync with the actual database? Use tools that generate the diagram from the schema rather than the other way around: prisma-erd-generator, SchemaSpy, Atlas, or schemahero. A diagram you maintain by hand will drift in days.

Do I need to resolve every N:N before drawing? No — at the conceptual stage you can leave Student }o--o{ Course as a many-to-many. When you move to the logical model, introduce the join table.

Which tools should I use to draw one? dbdiagram.io and DBML for text-driven diagrams, drawSQL for a polished web UI, MySQL Workbench / pgModeler for round-trip to a live DB, Mermaid for diagrams that live inside Markdown and Git.