---
title: "Entity-relationship diagram (ERD)"
description: "An entity-relationship diagram (ERD) is a visual representation of a database's entities (tables), their attributes, and the relationships between them, used to design and document relational schemas."
canonical: "https://motherduck.com/glossary/entity-relationship-diagram/"
related:
  - title: "Database Concepts | MotherDuck Docs"
    url: "https://motherduck.com/docs/concepts/database-concepts/"
  - title: "Analyze JSON Data Using SQL and DuckDB"
    url: "https://motherduck.com/blog/analyze-json-data-using-sql/"
  - title: "DuckLake: The Definitive Guide — Live Author Q&A with Matt Martin & Alex Monahan | MotherDuck"
    url: "https://motherduck.com/videos/ducklake-definitive-guide-oreilly-book/"
gated_asset:
  title: "DuckLake: The Lakehouse Table Format"
  url: "https://motherduck.com/lp/ducklake-lakehouse-table-format-book-full/"
---

# Entity-relationship diagram (ERD)

> An entity-relationship diagram (ERD) is a visual representation of a database's entities (tables), their attributes, and the relationships between them, used to design and document relational schemas.

## Overview

An entity-relationship diagram, introduced by Peter Chen in 1976, models a database as **entities** (things, which become tables), **attributes** (properties of an entity, which become columns), and **relationships** (associations between entities, which become foreign keys or junction tables). ERDs are typically drawn at three levels of increasing detail:

- **Conceptual**: entities and relationships only, no attributes or keys — a high-level map for discussing scope with stakeholders.
- **Logical**: adds attributes, primary keys, and cardinality (one-to-one, one-to-many, many-to-many), independent of any specific database technology.
- **Physical**: adds concrete data types, constraints, and indexes — the blueprint for the actual `CREATE TABLE` statements.

### Reading an ERD

Relationships carry **cardinality**: a `customer` places many `orders` (one-to-many), while `students` and `courses` relate many-to-many through an `enrollments` junction table. Crow's-foot notation is the most common way to show this visually, but the underlying idea maps directly to foreign keys in SQL.

### Example

A simple logical ERD — one customer places many orders, each order has many order lines, each line references one product — translates directly to:

```sql
CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    name VARCHAR
);

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(customer_id),
    order_date DATE
);

CREATE TABLE order_lines (
    order_id INTEGER REFERENCES orders(order_id),
    line_number INTEGER,
    product_id INTEGER,
    quantity INTEGER,
    PRIMARY KEY (order_id, line_number)
);
```

Each `REFERENCES` foreign key here is exactly the relationship line an ERD would draw between `orders` and `customers`, and between `order_lines` and `orders`.

### DuckDB angle

DuckDB has no built-in ERD visualizer, but you can inspect a live schema's tables, columns, and foreign keys through `information_schema` (or `DESCRIBE tablename`, and `duckdb_constraints()`), and feed that into any external ERD tool to generate a diagram from an existing DuckDB or MotherDuck database rather than drawing one from scratch.