Maowl Open Maowl

Northwind: the classic sales database

Northwind Traders is the fictional specialty foods company behind one of the best-known sample databases. Here's its sales database: customers, orders, products, suppliers, and employees.

Open this model in Maowl The Northwind sample model in Maowl, with the Layers tab open in the side panel

What the model shows

Customers place orders, employees sell them, and shippers deliver them. Products come from suppliers, belong to categories, and appear on orders through order lines. Employees report to a manager and cover sales territories, which belong to regions.

It's small enough to take in at a glance, yet it has the patterns most sales databases share: lookup tables, one-to-many foreign keys, and a table that refers to itself, as each employee refers to a manager. It's a good model to practice on before you plan your own.

Tables and columns

Category

Customer

Employee

EmployeeTerritory

Order

OrderDetail

Product

Region

Shipper

Supplier

Territory

Foreign keys

Layers

Design notes

SQL

Here's the PostgreSQL script Maowl exports for this model: one CREATE TABLE statement per table, with its primary key and foreign keys, then the indexes. Choose another database in the Export dialog, such as MySQL, SQL Server, or Snowflake, to get the same tables for it.

CREATE TABLE Supplier (
  supplierId INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  companyName VARCHAR(40) NOT NULL,
  contactName VARCHAR(30),
  contactTitle VARCHAR(30),
  address VARCHAR(60),
  city VARCHAR(15),
  region VARCHAR(15),
  postalCode VARCHAR(10),
  country VARCHAR(15),
  phone VARCHAR(24),
  fax VARCHAR(24),
  homePage TEXT
);

CREATE TABLE Category (
  categoryId INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  categoryName VARCHAR(15) NOT NULL UNIQUE,
  description TEXT,
  picture BYTEA,
  parentId INTEGER,
  CONSTRAINT fk_category_parent FOREIGN KEY (parentId) REFERENCES Category (categoryId)
);

CREATE TABLE Product (
  productId INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  productName VARCHAR(40) NOT NULL,
  supplierId INTEGER,
  categoryId INTEGER,
  quantityPerUnit VARCHAR(20),
  unitPrice NUMERIC(10,2) DEFAULT 0,
  unitsInStock SMALLINT DEFAULT 0,
  unitsOnOrder SMALLINT DEFAULT 0,
  reorderLevel SMALLINT DEFAULT 0,
  discontinued BOOLEAN DEFAULT false NOT NULL,
  CONSTRAINT chk_product_unit_price CHECK (unitPrice >= 0),
  CONSTRAINT fk_product_supplier FOREIGN KEY (supplierId) REFERENCES Supplier (supplierId),
  CONSTRAINT fk_product_category FOREIGN KEY (categoryId) REFERENCES Category (categoryId)
);

CREATE TABLE Customer (
  customerId CHAR(5) PRIMARY KEY,
  companyName VARCHAR(40) NOT NULL,
  contactName VARCHAR(30),
  contactTitle VARCHAR(30),
  address VARCHAR(60),
  city VARCHAR(15),
  region VARCHAR(15),
  postalCode VARCHAR(10),
  country VARCHAR(15),
  phone VARCHAR(24),
  fax VARCHAR(24)
);

CREATE TABLE Shipper (
  shipperId INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  companyName VARCHAR(40) NOT NULL,
  phone VARCHAR(24)
);

CREATE TABLE Employee (
  employeeId INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  lastName VARCHAR(20) NOT NULL,
  firstName VARCHAR(10) NOT NULL,
  title VARCHAR(30),
  titleOfCourtesy VARCHAR(25),
  birthDate DATE,
  hireDate DATE,
  address VARCHAR(60),
  city VARCHAR(15),
  region VARCHAR(15),
  postalCode VARCHAR(10),
  country VARCHAR(15),
  homePhone VARCHAR(24),
  extension VARCHAR(4),
  notes TEXT,
  reportsTo INTEGER,
  photoPath VARCHAR(255),
  CONSTRAINT fk_employee_manager FOREIGN KEY (reportsTo) REFERENCES Employee (employeeId)
);

CREATE TABLE "Order" (
  orderId INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  customerId CHAR(5),
  employeeId INTEGER,
  orderDate DATE,
  requiredDate DATE,
  shippedDate DATE,
  shipVia INTEGER,
  freight NUMERIC(10,2) DEFAULT 0,
  shipName VARCHAR(40),
  shipAddress VARCHAR(60),
  shipCity VARCHAR(15),
  shipRegion VARCHAR(15),
  shipPostalCode VARCHAR(10),
  shipCountry VARCHAR(15),
  CONSTRAINT fk_order_customer FOREIGN KEY (customerId) REFERENCES Customer (customerId),
  CONSTRAINT fk_order_employee FOREIGN KEY (employeeId) REFERENCES Employee (employeeId),
  CONSTRAINT fk_order_shipper FOREIGN KEY (shipVia) REFERENCES Shipper (shipperId)
);

CREATE TABLE OrderDetail (
  orderId INTEGER,
  productId INTEGER,
  unitPrice NUMERIC(10,2) NOT NULL,
  quantity SMALLINT DEFAULT 1 NOT NULL,
  discount REAL DEFAULT 0 NOT NULL,
  PRIMARY KEY (orderId, productId),
  CONSTRAINT chk_order_detail_quantity CHECK (quantity > 0),
  CONSTRAINT chk_order_detail_discount CHECK (discount >= 0 AND discount <= 1),
  CONSTRAINT fk_order_detail_order FOREIGN KEY (orderId) REFERENCES "Order" (orderId) ON DELETE CASCADE,
  CONSTRAINT fk_order_detail_product FOREIGN KEY (productId) REFERENCES Product (productId)
);

CREATE TABLE Region (
  regionId INTEGER PRIMARY KEY,
  regionDescription VARCHAR(50) NOT NULL
);

CREATE TABLE Territory (
  territoryId VARCHAR(20) PRIMARY KEY,
  territoryDescription VARCHAR(50) NOT NULL,
  regionId INTEGER NOT NULL,
  CONSTRAINT fk_territory_region FOREIGN KEY (regionId) REFERENCES Region (regionId)
);

CREATE TABLE EmployeeTerritory (
  employeeId INTEGER,
  territoryId VARCHAR(20),
  PRIMARY KEY (employeeId, territoryId),
  CONSTRAINT fk_employee_territory_employee FOREIGN KEY (employeeId) REFERENCES Employee (employeeId) ON DELETE CASCADE,
  CONSTRAINT fk_employee_territory_territory FOREIGN KEY (territoryId) REFERENCES Territory (territoryId) ON DELETE CASCADE
);

CREATE INDEX idx_supplier_company_name ON Supplier (companyName);
CREATE INDEX idx_product_name ON Product (productName);
CREATE INDEX idx_product_category ON Product (categoryId);
CREATE INDEX idx_customer_company_name ON Customer (companyName);
CREATE INDEX idx_customer_city ON Customer (city);
CREATE INDEX idx_employee_last_name ON Employee (lastName);
CREATE INDEX idx_order_date ON "Order" (orderDate);
CREATE INDEX idx_order_customer ON "Order" (customerId);

Source

The model is based on Microsoft's Northwind sample database and northwind_psql, its PostgreSQL port. Download the model as northwind.json.