← All schemas
Schema Library

Northwind Database Diagram

Northwind Traders is Microsoft's classic sample database - a food importer/exporter used in tutorials for decades - ported here to PostgreSQL. Customers place orders handled by employees; orders contain order_details lines referencing products; products come from suppliers and belong to categories; employees report to other employees and cover territories through a join table. Fourteen tables are mapped above. The detail that makes Northwind a great teaching schema: order_details uses a composite primary key of (order_id, product_id), and employees has a self-referencing reports_to foreign key - the two patterns every ER diagram course teaches.

Written by Varun Krishnan · Founder, dbdiagramr

Published · Updated

Source: Northwind Traders sample database, Microsoft schema ported to PostgreSQL by pthom/northwind_psql commit cd0ef28 (2024-08-31) · License: Ms-PL

Tables
14
Core flow
Customer -> Order -> Order Details
Origin
Microsoft sample, PG port

Northwind Traders schema from pthom/northwind_psql commit cd0ef28 (2024-08-31), originally published by Microsoft.

Indexes and the sample-data rows are omitted. PostgreSQL folds the port's unquoted uppercase source names to lowercase, which is how they appear here. northwind.sql.

Northwind database schema diagram showing every table, column, and foreign key

Scroll horizontally to see the full diagram. Click it to open at full size.

Open in DBdiagramr to edit →

Embed this diagram

Paste this HTML into a README, docs page, or blog post. The image links back to the full schema page.

<a href="https://www.dbdiagramr.space/schema/northwind">
  <img src="https://www.dbdiagramr.space/schemas/northwind-er-diagram.svg" alt="Northwind Database Diagram (PostgreSQL)" width="2666" height="1564" />
</a>
<p><a href="https://www.dbdiagramr.space/schema/northwind">Northwind Database Diagram (PostgreSQL)</a> by dbdiagramr</p>

Tables in the Northwind schema

ColumnTypeNullableKey
categories
category_idsmallintNoPK
category_namecharacter varying(15)No-
descriptiontextYes-
picturebyteaYes-
customer_customer_demo
customer_idcharacter varying(5)NoPK, FK → customers.customer_id
customer_type_idcharacter varying(5)NoPK, FK → customer_demographics.customer_type_id
customer_demographics
customer_type_idcharacter varying(5)NoPK
customer_desctextYes-
customers
customer_idcharacter varying(5)NoPK
company_namecharacter varying(40)No-
contact_namecharacter varying(30)Yes-
contact_titlecharacter varying(30)Yes-
addresscharacter varying(60)Yes-
citycharacter varying(15)Yes-
regioncharacter varying(15)Yes-
postal_codecharacter varying(10)Yes-
countrycharacter varying(15)Yes-
phonecharacter varying(24)Yes-
faxcharacter varying(24)Yes-
employees
employee_idsmallintNoPK
last_namecharacter varying(20)No-
first_namecharacter varying(10)No-
titlecharacter varying(30)Yes-
title_of_courtesycharacter varying(25)Yes-
birth_datedateYes-
hire_datedateYes-
addresscharacter varying(60)Yes-
citycharacter varying(15)Yes-
regioncharacter varying(15)Yes-
postal_codecharacter varying(10)Yes-
countrycharacter varying(15)Yes-
home_phonecharacter varying(24)Yes-
extensioncharacter varying(4)Yes-
photobyteaYes-
notestextYes-
reports_tosmallintYesFK → employees.employee_id
photo_pathcharacter varying(255)Yes-
employee_territories
employee_idsmallintNoPK, FK → employees.employee_id
territory_idcharacter varying(20)NoPK, FK → territories.territory_id
order_details
order_idsmallintNoPK, FK → orders.order_id
product_idsmallintNoPK, FK → products.product_id
unit_pricerealNo-
quantitysmallintNo-
discountrealNo-
orders
order_idsmallintNoPK
customer_idcharacter varying(5)YesFK → customers.customer_id
employee_idsmallintYesFK → employees.employee_id
order_datedateYes-
required_datedateYes-
shipped_datedateYes-
ship_viasmallintYesFK → shippers.shipper_id
freightrealYes-
ship_namecharacter varying(40)Yes-
ship_addresscharacter varying(60)Yes-
ship_citycharacter varying(15)Yes-
ship_regioncharacter varying(15)Yes-
ship_postal_codecharacter varying(10)Yes-
ship_countrycharacter varying(15)Yes-
products
product_idsmallintNoPK
product_namecharacter varying(40)No-
supplier_idsmallintYesFK → suppliers.supplier_id
category_idsmallintYesFK → categories.category_id
quantity_per_unitcharacter varying(20)Yes-
unit_pricerealYes-
units_in_stocksmallintYes-
units_on_ordersmallintYes-
reorder_levelsmallintYes-
discontinuedintegerNo-
region
region_idsmallintNoPK
region_descriptioncharacter varying(60)No-
shippers
shipper_idsmallintNoPK
company_namecharacter varying(40)No-
phonecharacter varying(24)Yes-
suppliers
supplier_idsmallintNoPK
company_namecharacter varying(40)No-
contact_namecharacter varying(30)Yes-
contact_titlecharacter varying(30)Yes-
addresscharacter varying(60)Yes-
citycharacter varying(15)Yes-
regioncharacter varying(15)Yes-
postal_codecharacter varying(10)Yes-
countrycharacter varying(15)Yes-
phonecharacter varying(24)Yes-
faxcharacter varying(24)Yes-
homepagetextYes-
territories
territory_idcharacter varying(20)NoPK
territory_descriptioncharacter varying(60)No-
region_idsmallintNoFK → region.region_id
us_states
state_idsmallintNoPK
state_namecharacter varying(100)Yes-
state_abbrcharacter varying(2)Yes-
state_regioncharacter varying(50)Yes-

Table relationships

10 relationships connect the tables in this schema. Each entry shows the child columns, the parent columns they reference, and the cardinality.

Many-to-many

customer_customer_demo (customer_type_id -> customer_demographics.customer_type_id, customer_id -> customers.customer_id)

Many customer demographics can have many customers through customer_customer_demo (customer_customer_demo.customer_type_id -> customer_demographics.customer_type_id, customer_customer_demo.customer_id -> customers.customer_id).

Self-referencing

employees.reports_to → employees.employee_id

One employee can have many child employees (employees.reports_to -> employees.employee_id, optional).

Many-to-many

employee_territories (territory_id -> territories.territory_id, employee_id -> employees.employee_id)

Many territories can have many employees through employee_territories (employee_territories.territory_id -> territories.territory_id, employee_territories.employee_id -> employees.employee_id).

Many-to-many

order_details (product_id -> products.product_id, order_id -> orders.order_id)

Many products can have many orders through order_details (order_details.product_id -> products.product_id, order_details.order_id -> orders.order_id).

One-to-many

orders.customer_id → customers.customer_id

One customer can have many orders (orders.customer_id -> customers.customer_id, optional).

One-to-many

orders.employee_id → employees.employee_id

One employee can have many orders (orders.employee_id -> employees.employee_id, optional).

One-to-many

orders.ship_via → shippers.shipper_id

One shipper can have many orders (orders.ship_via -> shippers.shipper_id, optional).

One-to-many

products.category_id → categories.category_id

One category can have many products (products.category_id -> categories.category_id, optional).

One-to-many

products.supplier_id → suppliers.supplier_id

One supplier can have many products (products.supplier_id -> suppliers.supplier_id, optional).

One-to-many

territories.region_id → region.region_id

One region can have many territories (territories.region_id -> region.region_id, required).

Common queries

These PostgreSQL queries run against the schema shown on this page.

Revenue by category

Sum order-line value grouped by product category.

select c.category_name,
       sum(od.unit_price * od.quantity * (1 - od.discount)) as revenue
from order_details od
join products p on p.product_id = od.product_id
join categories c on c.category_id = p.category_id
group by c.category_name
order by revenue desc;

Top customers

Rank customers by order-line value.

select c.company_name,
       sum(od.unit_price * od.quantity * (1 - od.discount)) as revenue
from orders o
join order_details od on od.order_id = o.order_id
join customers c on c.customer_id = o.customer_id
group by c.customer_id, c.company_name
order by revenue desc;

Employee sales

Rank employees by the value they sold.

select e.first_name || ' ' || e.last_name as employee,
       sum(od.unit_price * od.quantity * (1 - od.discount)) as revenue
from orders o
join order_details od on od.order_id = o.order_id
join employees e on e.employee_id = o.employee_id
group by e.employee_id, e.first_name, e.last_name
order by revenue desc;

Low stock products

Products at or below their reorder level.

select product_name, units_in_stock, reorder_level
from products
where units_in_stock <= reorder_level
order by units_in_stock, product_name;

Frequently asked questions

Related schema diagrams

Visualize your own database

Paste your PostgreSQL connection string and get an interactive ER diagram of your own schema in under 10 seconds. No signup required.

Try it free →