Building a Sales Database in PostgreSQL — Schema, Data & JOIN Examples

Building a Sales Database in PostgreSQL — Schema, Data & JOIN Examples

A compact, practical guide to modeling a small sales system in PostgreSQL. This post includes:

• a clean schema for salesDB,
• realistic sample data you can load,
• psql tips and a transcript explanation,
• multiple LEFT JOIN examples and why they behave the way they do,
• useful analytics queries and common gotchas.

Overview

Entities

• Products — items for sale
• Customers — buyers
• Employees — sales staff (self-referencing manager)
• Orders — transactions that link a product, a customer, and a salesperson

Conceptual ERD

Products (1) ───< Orders >─── (1) Customers
                     │
                     ▼
                 Employees

Each orders row references:

• products.productid
• customers.customerid
• employees.employeeid (salesperson)

Create the database

-- run as a role that can create a DB
CREATE DATABASE salesDB;
-- then connect:
-- \c salesDB

Schema (Postgres SQL)

-- PRODUCTS
CREATE TABLE products (
    productid    BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    product_name TEXT NOT NULL,
    category     TEXT,
    price        NUMERIC(12,2) CHECK (price >= 0)
);

-- CUSTOMERS
CREATE TABLE customers (
    customerid   BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    firstname    TEXT NOT NULL,
    lastname     TEXT NOT NULL,
    country      TEXT,
    score        INTEGER CHECK (score >= 0) DEFAULT 0
);

-- EMPLOYEES (self-referencing manager)
CREATE TABLE employees (
    employeeid   BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    firstname    TEXT NOT NULL,
    lastname     TEXT NOT NULL,
    department   TEXT,
    birthdate    DATE,
    gender       VARCHAR(10),
    salary       NUMERIC(12,2) CHECK (salary >= 0),
    managerid    BIGINT,
    CONSTRAINT fk_manager FOREIGN KEY (managerid)
        REFERENCES employees(employeeid)
        ON DELETE SET NULL
);

-- ORDERS
CREATE TABLE orders (
    orderid       BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    productid     BIGINT NOT NULL,
    customerid    BIGINT NOT NULL,
    salespersonid BIGINT,
    orderdate     DATE,
    shipdate      DATE,
    orderstatus   TEXT,
    shipaddress   TEXT,
    billaddress   TEXT,
    quantity      INTEGER CHECK (quantity >= 0) DEFAULT 1,
    sales         NUMERIC(14,2) CHECK (sales >= 0),
    creationtime  TIMESTAMP WITHOUT TIME ZONE DEFAULT now(),
    CONSTRAINT fk_orders_product FOREIGN KEY (productid)
        REFERENCES products(productid) ON DELETE RESTRICT,
    CONSTRAINT fk_orders_customer FOREIGN KEY (customerid)
        REFERENCES customers(customerid) ON DELETE RESTRICT,
    CONSTRAINT fk_orders_salesperson FOREIGN KEY (salespersonid)
        REFERENCES employees(employeeid) ON DELETE SET NULL
);

-- Indexes to speed joins
CREATE INDEX idx_orders_productid ON orders(productid);
CREATE INDEX idx_orders_customerid ON orders(customerid);
CREATE INDEX idx_orders_salespersonid ON orders(salespersonid);
CREATE INDEX idx_employees_managerid ON employees(managerid);

Notes

• BIGINT GENERATED ALWAYS AS IDENTITY is the modern, portable alternative to serial.
• ON DELETE RESTRICT prevents deleting referenced products/customers; change to CASCADE if you need automatic deletes.
• Consider a Postgres ENUM for orderstatus if you want stricter, validated values.

Sample data (expanded)

-- PRODUCTS (10)
INSERT INTO products (product_name, category, price) VALUES
('Widget', 'Gadgets', 19.99),
('Gizmo', 'Gadgets', 29.50),
('Chair', 'Furniture', 120.00),
('Desk', 'Furniture', 250.00),
('Laptop', 'Electronics', 899.99),
('Mouse', 'Electronics', 25.99),
('Keyboard', 'Electronics', 49.99),
('Headphones', 'Audio', 79.00),
('Smartwatch', 'Wearables', 199.99),
('Sofa', 'Furniture', 599.00);

-- CUSTOMERS (10)
INSERT INTO customers (firstname, lastname, country, score) VALUES
('Alice','Anderson','USA',87),
('Bob','Brown','USA',72),
('Carol','Clark','UK',95),
('David','Davis','Canada',67),
('Eve','Evans','Australia',92),
('Frank','Foster','Germany',75),
('Grace','Green','France',89),
('Henry','Hill','USA',80),
('Ivy','Irwin','UK',90),
('Jack','Johnson','Japan',85);

-- EMPLOYEES (8)
INSERT INTO employees (firstname, lastname, department, birthdate, gender, salary, managerid) VALUES
('Sam','Smith','Sales','1988-05-20','M',45000,NULL),
('Jill','Jones','Sales','1990-09-10','F',47000,1),
('Peter','Parker','Sales','1992-01-15','M',44000,1),
('Nina','Nelson','Support','1985-12-25','F',52000,NULL),
('Oscar','Owens','Sales','1989-04-10','M',48000,1),
('Liam','Lopez','Sales','1993-03-03','M',41000,2),
('Mia','Moore','Marketing','1991-06-07','F',55000,NULL),
('Noah','Nguyen','Sales','1994-02-18','M',39000,2);

-- ORDERS (16)
INSERT INTO orders (productid, customerid, salespersonid, orderdate, shipdate, orderstatus, shipaddress, billaddress, quantity, sales) VALUES
(1,1,1,'2025-10-01','2025-10-03','shipped','123 Main St','123 Main St',2,39.98),
(3,2,2,'2025-10-02',NULL,'processing','456 Oak Ave','456 Oak Ave',1,120.00),
(5,3,1,'2025-09-29','2025-10-04','shipped','78 Elm St','78 Elm St',1,899.99),
(6,4,3,'2025-10-03','2025-10-05','shipped','12 Maple Rd','12 Maple Rd',3,77.97),
(8,5,2,'2025-10-04',NULL,'processing','9 Queen St','9 Queen St',1,79.00),
(10,6,4,'2025-09-25','2025-09-28','shipped','54 King Ave','54 King Ave',1,599.00),
(2,7,5,'2025-09-30','2025-10-02','shipped','1 River Rd','1 River Rd',2,59.00),
(4,8,3,'2025-09-20','2025-09-25','cancelled','321 Pine St','321 Pine St',1,250.00),
(7,9,2,'2025-10-01',NULL,'processing','67 Hill Rd','67 Hill Rd',1,49.99),
(9,10,5,'2025-10-03','2025-10-05','shipped','789 Lake St','789 Lake St',2,399.98),
(1,5,3,'2025-10-04',NULL,'new','9 Queen St','9 Queen St',1,19.99),
(6,7,4,'2025-10-01','2025-10-03','shipped','1 River Rd','1 River Rd',2,51.98),
(8,8,2,'2025-10-02','2025-10-06','shipped','321 Pine St','321 Pine St',1,79.00),
(10,1,5,'2025-09-18','2025-09-22','shipped','123 Main St','123 Main St',1,599.00),
(5,9,1,'2025-09-29','2025-10-01','shipped','67 Hill Rd','67 Hill Rd',1,899.99),
(3,4,3,'2025-10-05',NULL,'processing','12 Maple Rd','12 Maple Rd',2,240.00);

psql quick checks & common pitfall

Run \dt to list tables in the current DB and schema (usually public).

Pitfall you may see:

select orderId from salesdb.orders limit 2;
-- ERROR: relation "salesdb.orders" does not exist

Why: salesdb is a database name, not a schema. Fully qualified table names are schema.table (e.g. public.orders). When connected to salesDB, just use:

SELECT orderid FROM orders LIMIT 2;

Multiple LEFT JOIN — progressive example

Start small and add joins one by one. LEFT JOIN keeps all rows from the left table and adds matches from the right tables.

1) Orders only

SELECT o.orderid, o.sales
FROM orders o
LIMIT 3;

2) Add customer info

SELECT o.orderid, o.sales, c.firstname, c.lastname
FROM orders o
LEFT JOIN customers c ON o.customerid = c.customerid
LIMIT 3;

3) Add product details (two LEFT JOINs)

SELECT o.orderid, o.sales, c.firstname, c.lastname, p.product_name
FROM orders o
LEFT JOIN customers c ON o.customerid = c.customerid
LEFT JOIN products p ON o.productid = p.productid
LIMIT 3;

4) Full order row with salesperson

SELECT
  o.orderid,
  o.orderdate,
  c.firstname || ' ' || c.lastname AS customer_name,
  p.product_name,
  p.category,
  o.quantity,
  o.sales,
  e.firstname || ' ' || e.lastname AS salesperson,
  o.orderstatus
FROM orders o
LEFT JOIN customers c ON o.customerid = c.customerid
LEFT JOIN products  p ON o.productid = p.productid
LEFT JOIN employees e ON o.salespersonid = e.employeeid
ORDER BY o.orderdate DESC
LIMIT 10;

Why LEFT JOIN for employees? If salespersonid is NULL (unassigned) or references a removed employee, the employee columns become NULL but the orders row is preserved.

Implementing anti-joins (non-matching rows)

• Left anti-join (rows in A with no match in B):

SELECT a.*
FROM A a
LEFT JOIN B b ON a.id = b.a_id
WHERE b.a_id IS NULL;

• Full anti-join (rows in either table that have no match in the other):

SELECT *
FROM A
FULL JOIN B ON A.id = B.a_id
WHERE A.id IS NULL OR B.a_id IS NULL;

Alternatively use EXCEPT for "A but not B" type checks.

---

Useful analytics queries

1 All orders with details

SELECT 
  o.orderid,
  c.firstname || ' ' || c.lastname AS customer_name,
  p.product_name,
  o.quantity,
  o.sales,
  o.orderstatus,
  e.firstname || ' ' || e.lastname AS salesperson
FROM orders o
LEFT JOIN customers c ON o.customerid = c.customerid
LEFT JOIN products  p ON o.productid = p.productid
LEFT JOIN employees e ON o.salespersonid = e.employeeid
ORDER BY o.orderdate DESC;

2 Total sales by country

SELECT c.country, SUM(o.sales) AS total_sales
FROM orders o
JOIN customers c ON o.customerid = c.customerid
GROUP BY c.country
ORDER BY total_sales DESC;

3 Top 5 selling products

SELECT p.product_name, SUM(o.sales) AS total_sales, SUM(o.quantity) AS total_units
FROM orders o
JOIN products p ON o.productid = p.productid
GROUP BY p.product_name
ORDER BY total_sales DESC
LIMIT 5;

Thanks for your reading.