Northwind Database ER diagram

What each table does

• customersThe famous char(5) mnemonic key (ALFKI), from the era of natural keys.
• ordersThe header: who ordered, who took it, who ships it, three lifecycle dates.
• order_detailsThe junction with payload: quantity, a frozen unit_price and a discount fraction.
• productsThe catalog, pointing at both a supplier and a category.
• suppliers / categoriesThe two lookup tables the catalog points at. Every product carries a supplier_id and a category_id.
• employeesreports_to references employees itself, which puts an org chart in one column.
• shippersThe delivery companies. Orders reference them through ship_via, the foreign key whose name breaks the _id convention.
• territories / regionsSales geography, two levels deep.
• employee_territoriesPure many-to-many between employees and territories.
• customer_demographics / customer_customer_demoThe 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 is a foreign key no naming convention would find. The column is declared as ship_via int REFERENCES shippers(shipper_id), and nothing in the name says shippers. A tool that matches *_id names loses this relation, because only the constraint draws the line. 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. You are free to copy it.

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

• AdventureWorks Database ER diagram
• Sakila Database ER diagram
• All example schemas
• Data dictionary generator: the documentation side of the same tool.