Northwind Database ER diagram

What each table does

• customers — The famous char(5) mnemonic key — ALFKI — from the era of natural keys.
• orders — The header: who ordered, who took it, who ships it, three lifecycle dates.
• order_details — The junction with payload — quantity, a frozen unit_price and a discount fraction.
• products — The catalog, pointing at both a supplier and a category.
• suppliers / categories — The two lookup tables the catalog points at — every product carries a supplier_id and a category_id.
• employees — reports_to references employees itself — an org chart in one column.
• shippers — The delivery companies; orders reference them through ship_via — the foreign key whose name breaks the _id convention.
• territories / regions — Sales geography, two levels deep.
• employee_territories — Pure many-to-many between employees and territories.
• customer_demographics / customer_customer_demo — The vestigial pair: shipped empty for decades, kept because tutorials reference it.

Design decisions worth copying

• customers.customer_id is char(5) and mnemonic (ALFKI, ANATR) — a natural key from 1994. Modern schemas use surrogate ids and keep the mnemonic as a unique column, but Northwind is the cleanest place to see why natural keys felt right and what makes them fragile: renaming a company breaks every reference.
• orders.ship_via proves why declared foreign keys beat naming conventions: the column is declared as ship_via int REFERENCES shippers(shipper_id), and nothing in the name says shippers — only the constraint draws the line. A tool that matches *_id names loses this relation; paste the schema with the REFERENCES removed and watch the line disappear.
• The original table is called "Order Details" — with a space — so every port either quotes the identifier forever or renames it. This rendition renames it to order_details, which is what most PostgreSQL and MySQL ports do; remember the space when a tutorial query fails against a port.
• employees.reports_to is the canonical self-reference: one nullable column turns a flat table into a hierarchy, and the NULL at the top is the CEO. The same shape reappears in category trees and comment threads.

The SQL

PostgreSQL dialect; paste it into the tool above (it is already there), into your own database, or into a migration. Copy freely — example schemas are meant to be taken.

CREATE TABLE regions (
    region_id    serial      PRIMARY KEY,
    description  varchar(60) NOT NULL
);

CREATE TABLE territories (
    territory_id  varchar(20) PRIMARY KEY,
    description   varchar(60) NOT NULL,
    region_id     int         NOT NULL REFERENCES regions(region_id)
);

CREATE TABLE categories (
    category_id  serial       PRIMARY KEY,
    name         varchar(30)  NOT NULL,
    description  text
);

CREATE TABLE suppliers (
    supplier_id   serial       PRIMARY KEY,
    company_name  varchar(60)  NOT NULL,
    contact_name  varchar(60),
    city          varchar(30),
    country       varchar(30)
);

CREATE TABLE products (
    product_id        serial        PRIMARY KEY,
    name              varchar(60)   NOT NULL,
    supplier_id       int           REFERENCES suppliers(supplier_id),
    category_id       int           REFERENCES categories(category_id),
    quantity_per_unit varchar(30),
    unit_price        numeric(10,2),
    units_in_stock    smallint,
    discontinued      boolean       NOT NULL DEFAULT false
);
COMMENT ON COLUMN products.discontinued IS 'The original bit flag; kept rows, never deletes';

CREATE TABLE customers (
    customer_id   char(5)      PRIMARY KEY,
    company_name  varchar(60)  NOT NULL,
    contact_name  varchar(60),
    city          varchar(30),
    country       varchar(30)
);
COMMENT ON COLUMN customers.customer_id IS 'Mnemonic natural key: ALFKI for Alfreds Futterkiste';

CREATE TABLE customer_demographics (
    customer_type_id  char(10) PRIMARY KEY,
    customer_desc     text
);

CREATE TABLE customer_customer_demo (
    customer_id       char(5)  NOT NULL REFERENCES customers(customer_id),
    customer_type_id  char(10) NOT NULL REFERENCES customer_demographics(customer_type_id),
    PRIMARY KEY (customer_id, customer_type_id)
);

CREATE TABLE employees (
    employee_id  serial      PRIMARY KEY,
    last_name    varchar(20) NOT NULL,
    first_name   varchar(10) NOT NULL,
    title        varchar(30),
    hire_date    date,
    reports_to   int         REFERENCES employees(employee_id)
);
COMMENT ON COLUMN employees.reports_to IS 'Self-reference: the org chart lives inside the table';

CREATE TABLE employee_territories (
    employee_id   int         NOT NULL REFERENCES employees(employee_id),
    territory_id  varchar(20) NOT NULL REFERENCES territories(territory_id),
    PRIMARY KEY (employee_id, territory_id)
);

CREATE TABLE shippers (
    shipper_id    serial      PRIMARY KEY,
    company_name  varchar(40) NOT NULL,
    phone         varchar(24)
);

CREATE TABLE orders (
    order_id       serial        PRIMARY KEY,
    customer_id    char(5)       REFERENCES customers(customer_id),
    employee_id    int           REFERENCES employees(employee_id),
    order_date     date,
    required_date  date,
    shipped_date   date,
    ship_via       int           REFERENCES shippers(shipper_id),
    freight        numeric(10,2),
    ship_city      varchar(30),
    ship_country   varchar(30)
);
COMMENT ON COLUMN orders.ship_via IS 'An FK that does not end in _id — name conventions cannot find it, the constraint can';

CREATE TABLE order_details (
    order_id    int           NOT NULL REFERENCES orders(order_id),
    product_id  int           NOT NULL REFERENCES products(product_id),
    unit_price  numeric(10,2) NOT NULL,
    quantity    smallint      NOT NULL DEFAULT 1,
    discount    real          NOT NULL DEFAULT 0,
    PRIMARY KEY (order_id, product_id)
);
COMMENT ON TABLE order_details IS 'The original calls this "Order Details", space and all — renditions rename it';

FAQ

More example schemas

• E-commerce Store ER diagram
• Blog Platform ER diagram
• All example schemas
• Data dictionary generator — the documentation side of the same tool.