DEV Community

Sajjad Rahman
Sajjad Rahman

Posted on

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

#database #postgres #dataengineering #sql

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

2 table join

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;
Enter fullscreen mode Exit fullscreen mode

output of limit

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;
Enter fullscreen mode Exit fullscreen mode
  • 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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

all orders

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;
Enter fullscreen mode Exit fullscreen mode

total sales by countrt

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;
Enter fullscreen mode Exit fullscreen mode

Top 5 selling products

Thanks for your reading.

Top comments (0)