Entity-relationship diagram (ERD)

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:

Copy code
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.