What is it? #
Goal: build a small but genuinely correct database for users and their orders, from an empty database to one you can query confidently.
This is the first project because almost every application contains this shape. Getting it right here means getting it right everywhere.
"Correct" specifically means: every table has a primary key, every relationship is enforced by a foreign key, invalid data is rejected by constraints rather than by hope, and the queries you will actually run are supported by indexes.
You should be able to finish this in one sitting. Take the time to type the statements rather than pasting them — the errors you make and fix are the point.
Think of it like this #
Building a small set of shelves before attempting a wardrobe.
The joints are the same, the measuring is the same, and the mistakes are cheap. Get the joints right at this scale and the larger piece is mostly more of the same.
Simple example #
Two core tables — users and orders — plus a products table and an order_items junction table, because an order containing several products is the most common shape you will meet.
By the end you will have roughly 50 users, 200 orders and a few thousand order items, and a set of queries answering real questions about them.
Code #
---------- ARCHITECTURE ----------
users orders order_items products
┌──────────┐ ┌────────────┐ ┌─────────────┐ ┌─────────┐
│ id PK │◀───────────│ user_id FK │◀────────│ order_id FK │ │ id PK │
│ email U │ │ id PK │ │ product_id │─────▶│ name │
│ name │ │ status │ │ quantity │ │ price │
│ created │ │ placed_at │ │ unit_price │ └─────────┘
└──────────┘ └────────────┘ │ PK(order,prod)│
└─────────────┘
one user ──▶ many orders one order ──▶ many items ◀── many products# ---------- STEP 1: create the database ----------
createdb -U postgres shop_project
psql -U postgres -d shop_project-- ---------- STEP 2: the schema ----------
CREATE TABLE users (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email text NOT NULL UNIQUE,
full_name text NOT NULL,
country char(2) NOT NULL DEFAULT 'IN',
created_at timestamptz NOT NULL DEFAULT now(),
CONSTRAINT users_email_shape CHECK (position('@' in email) > 1)
);
CREATE TABLE products (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL,
price numeric(10,2) NOT NULL CHECK (price >= 0), -- numeric, NEVER float
active boolean NOT NULL DEFAULT true
);
CREATE TABLE orders (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
user_id bigint NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
status text NOT NULL DEFAULT 'pending'
CHECK (status IN ('pending','paid','shipped','cancelled')),
placed_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE order_items (
order_id bigint NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
product_id bigint NOT NULL REFERENCES products(id) ON DELETE RESTRICT,
quantity integer NOT NULL CHECK (quantity > 0),
unit_price numeric(10,2) NOT NULL CHECK (unit_price >= 0),
PRIMARY KEY (order_id, product_id)
);
-- WHY each ON DELETE differs:
-- users RESTRICT - never silently lose a customer's order history
-- orders CASCADE - an item has no meaning without its order
-- products RESTRICT - deleting a product must not destroy past invoices-- ---------- STEP 3: indexes PostgreSQL will NOT create for you ----------
CREATE INDEX orders_user_id_idx ON orders (user_id);
CREATE INDEX orders_user_placed_idx ON orders (user_id, placed_at DESC);
CREATE INDEX order_items_product_idx ON order_items (product_id);
CREATE INDEX orders_pending_idx ON orders (placed_at) WHERE status = 'pending';
-- Primary keys are indexed automatically. FOREIGN KEYS ARE NOT.
-- The composite (user_id, placed_at DESC) serves "this user's recent orders",
-- which is the query an application runs constantly.-- ---------- STEP 4: seed data ----------
INSERT INTO users (email, full_name, country)
SELECT 'user' || i || '@example.com',
'User ' || i,
(ARRAY['IN','US','GB'])[1 + (i % 3)]
FROM generate_series(1, 50) AS i;
INSERT INTO products (name, price)
SELECT 'Product ' || i, round((random() * 900 + 100)::numeric, 2)
FROM generate_series(1, 20) AS i;
INSERT INTO orders (user_id, status, placed_at)
SELECT 1 + (random() * 49)::int,
(ARRAY['pending','paid','shipped','cancelled'])[1 + (random()*3)::int],
now() - (random() * interval '180 days')
FROM generate_series(1, 200);
-- Items: 1-4 per order, priced from the product at the time of sale
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
SELECT DISTINCT ON (o.id, p.id)
o.id, p.id,
1 + (random() * 3)::int,
p.price
FROM orders o
CROSS JOIN LATERAL (
SELECT id, price FROM products ORDER BY random() LIMIT 1 + (random()*3)::int
) p;
ANALYZE; -- give the planner statistics for the data you just loaded-- ---------- STEP 5: prove the constraints work ----------
-- Each of these SHOULD fail. Read the error messages.
INSERT INTO users (email, full_name) VALUES ('nope', 'Bad Email');
-- violates check constraint "users_email_shape"
INSERT INTO users (email, full_name) VALUES ('[email protected]', 'Duplicate');
-- violates unique constraint "users_email_key"
INSERT INTO orders (user_id) VALUES (99999);
-- violates foreign key constraint "orders_user_id_fkey"
INSERT INTO orders (user_id, status) VALUES (1, 'teleported');
-- violates check constraint "orders_status_check"
DELETE FROM users WHERE id = 1;
-- violates foreign key constraint on orders (ON DELETE RESTRICT working)-- ---------- STEP 6: the queries that matter ----------
-- Order totals, computed from the items
SELECT o.id, o.status, o.placed_at,
sum(i.quantity * i.unit_price) AS order_total
FROM orders o
JOIN order_items i ON i.order_id = o.id
GROUP BY o.id
ORDER BY o.placed_at DESC
LIMIT 10;
-- Every user with lifetime spend, INCLUDING those who never ordered
SELECT u.id, u.full_name,
count(DISTINCT o.id) AS orders,
COALESCE(sum(i.quantity * i.unit_price), 0) AS lifetime_spend
FROM users u
LEFT JOIN orders o ON o.user_id = u.id AND o.status <> 'cancelled'
LEFT JOIN order_items i ON i.order_id = o.id
GROUP BY u.id, u.full_name
ORDER BY lifetime_spend DESC
LIMIT 10;
-- Users who have never ordered
SELECT u.full_name FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);
-- Best-selling products
SELECT p.name,
sum(i.quantity) AS units,
sum(i.quantity * i.unit_price) AS revenue
FROM products p
JOIN order_items i ON i.product_id = p.id
GROUP BY p.id, p.name
ORDER BY revenue DESC
LIMIT 5;
-- Revenue per month
SELECT date_trunc('month', o.placed_at)::date AS month,
count(DISTINCT o.id) AS orders,
sum(i.quantity * i.unit_price) AS revenue
FROM orders o
JOIN order_items i ON i.order_id = o.id
WHERE o.status IN ('paid','shipped')
GROUP BY 1
ORDER BY 1 DESC;-- ---------- STEP 7: confirm the indexes are used ----------
EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 7 ORDER BY placed_at DESC LIMIT 10;
-- Look for: Index Scan using orders_user_placed_idx
-- On only 200 rows PostgreSQL may still choose a Seq Scan — and be right.
-- To see the index genuinely win, load 500,000 orders and compare.How it works #
Expected result: four tables, five indexes, roughly 50 users, 200 orders and several hundred order items, with every query above returning sensible data.
The design decisions worth understanding rather than copying:
unit_price lives on order_items, not on products alone. The price stored is the price at the time of sale. Without this, raising a product's price tomorrow silently rewrites the totals of every historical order — a genuinely serious accounting bug that is invisible until someone reconciles the books.
The composite primary key on order_items does two jobs at once: it identifies the pairing, and it prevents the same product being added twice to one order. It also creates an index on (order_id, product_id), which is why only the reverse direction needs its own index.
The three different ON DELETE behaviours are deliberate. Cascading from orders to items is right because an item is meaningless alone. Restricting from users and products is right because both would destroy history. A blanket policy either way would be wrong somewhere.
The order total is computed, not stored. Storing it would require keeping it in step with the items forever, through every edit, refund and correction. Computing it means it cannot disagree with the data it comes from. If that query later becomes too slow, a generated column or materialised view is the answer — not a manually maintained column.
ANALYZE after seeding matters even here. Without statistics, the planner guesses, and your EXPLAIN output will be misleading.
On 200 rows PostgreSQL will often choose a sequential scan over your indexes, and that is correct behaviour — reading a tiny table outright is cheaper than index indirection. This is exactly why testing on realistic data volumes matters, and why the third project loads far more data.
Real-world use #
This schema shape — parent, child, junction, catalogue — is the backbone of most business applications. Invoices and line items, playlists and tracks, courses and enrolments are all the same structure with different names.
The constraints are what make it survive contact with reality. Over a few years, a database is written to by the original application, a second service, a data migration, an admin fixing something by hand, and a script someone wrote once. The constraints are the only rules that apply to all of them.
The detail most commonly got wrong in real systems is the historical price. It is easy to join to products for the current price and much harder to explain, two years later, why last year's invoices no longer add up.
The second most common is missing foreign key indexes. It is invisible at 200 rows and painful at two million, where both the join and any parent deletion become table scans.
Common mistakes #
- Reading the current price from products instead of storing unit_price at the time of sale.
- Forgetting to index foreign keys, which only becomes visible once the tables are large.
- Storing a computed order total that can drift out of step with the line items.
- Applying one blanket ON DELETE policy instead of choosing per relationship.
- Testing query plans on a few hundred rows and concluding the indexes are not working.
Practice #
Extend the project to answer these, writing the SQL yourself. Which users placed an order in the last 30 days but not in the 30 days before that? What is the average number of items per order, by status? Which two products are most often bought together in the same order? Add a shipping_address requirement where each order must have exactly one address — decide whether that is a column set or a separate table, and justify the choice. Finally, add a constraint preventing an order from being marked shipped while it has no items, and explain why that is harder than the other constraints you wrote.