Database design starts with the entities of the business domain and the relationships between them: 1:1 is a foreign key that is also the primary key, 1:N is a foreign key on the “many” side, and N:M is a separate junction table with a composite primary key. For every foreign key you choose what happens on delete: RESTRICT for history, CASCADE for parts of a whole, SET NULL for optional references. Normalising to third normal form removes duplicates and contradictions; denormalise deliberately, in a way that lets the database keep the copy consistent with its source.
After this you will be able to
- turn a service description into entities and implement 1:1, 1:N and N:M relationships with foreign keys and a junction table
- choose a primary key (
bigint,uuidor natural) and anON DELETErule for every foreign key, and justify the choice - bring a flat table to 3NF and move the data into the new schema with
INSERT … SELECT - tell justified denormalisation (a generated column, a materialized view, a price snapshot) from a source of anomalies
Know beforehand
Verified with
PostgreSQL 17.11 (postgres:17-alpine)PostgreSQL 18.6 — сверкаbtree_gist (contrib PostgreSQL 17)
Checked against docs: October 11, 2026
Contents
From the business domain to entities
Design the schema before the first line of code: fixing it later, with live data in the tables, costs far more. Start by describing the business in plain words. Our example is an online booking service for a salon: “Clients book appointments with masters for services. Each master performs their own set of services. An appointment is a specific time with a specific master for one service at the price valid when it was booked. A master has a profile for the website. A client may come on another client’s recommendation.”
The nouns in such a description are candidate entities (future tables): client, master, service, appointment, profile. Verbs and words like “each”, “own” and “one” hint at relationships and their cardinality. Attributes are what we know about an entity: a client’s name and phone, a service’s duration and price, an appointment’s time.
- 01clients, masters, servicesIndependent entities with their own primary key `id`; a client has a unique phone and an optional link to whoever recommended them
- 02master_profiles — 1:1The primary key `master_id` is also a foreign key to `masters`: a master cannot have a second profile
- 03master_services — N:MThe master ↔ service junction table with a composite primary key `(master_id, service_id)`
- 04appointments — 1:NReferences the client and the master–service pair; an exclusion constraint stops one master’s appointments from overlapping
Note that an appointment does not reference the master and the service separately but their pair in master_services. That way the database itself enforces the rule “you can only book a service this master performs” — no check in the application code needed.
1:1, 1:N and N:M relationships in DDL
1:1
- Example in the service
- master — profile
- How to implement
- A foreign key that is also the primary key (or
UNIQUE):master_profiles.master_id
1:N
- Example in the service
- client — appointments
- How to implement
- A foreign key on the “many” side:
appointments.client_id
N:M
- Example in the service
- masters — services
- How to implement
- A junction table with two foreign keys and a composite primary key:
master_services
Self-reference
- Example in the service
- client — who recommended them
- How to implement
- A foreign key to the same table:
clients.referred_by_id
Below is the whole service schema. A foreign key (REFERENCES) must point to a primary key or to columns with a UNIQUE constraint — which is why appointments can reference the pair (master_id, service_id): it is the primary key of master_services. The EXCLUDE constraint forbids two rows with the same master and overlapping time ranges; comparing bigint with = in a GiST index needs the btree_gist extension. The docs mark it as trusted, so the database owner can install it, not only a superuser.
-- Online booking service: clients, masters, services, appointments
CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TABLE clients (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL CHECK (name <> ''),
phone text NOT NULL UNIQUE,
-- who recommended the salon: an optional link to another client
referred_by_id bigint REFERENCES clients (id) ON DELETE SET NULL,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE masters (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL,
is_active boolean NOT NULL DEFAULT true
);
-- 1:1 — the profile's primary key is also a foreign key to the master
CREATE TABLE master_profiles (
master_id bigint PRIMARY KEY REFERENCES masters (id) ON DELETE CASCADE,
bio text,
photo_url text
);
CREATE TABLE services (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL UNIQUE,
duration_min integer NOT NULL CHECK (duration_min > 0),
price numeric(10,2) NOT NULL CHECK (price > 0)
);
-- N:M — which services a master performs
CREATE TABLE master_services (
master_id bigint NOT NULL REFERENCES masters (id) ON DELETE CASCADE,
service_id bigint NOT NULL REFERENCES services (id) ON DELETE CASCADE,
PRIMARY KEY (master_id, service_id)
);
-- 1:N from the client and from the master–service pair
CREATE TABLE appointments (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
client_id bigint NOT NULL REFERENCES clients (id) ON DELETE RESTRICT,
master_id bigint NOT NULL,
service_id bigint NOT NULL,
starts_at timestamptz NOT NULL,
ends_at timestamptz NOT NULL,
price numeric(10,2) NOT NULL CHECK (price >= 0),
status text NOT NULL DEFAULT 'booked'
CHECK (status IN ('booked', 'done', 'cancelled', 'no_show')),
CHECK (ends_at > starts_at),
-- you can only book a service this master actually performs
FOREIGN KEY (master_id, service_id)
REFERENCES master_services (master_id, service_id) ON DELETE RESTRICT,
-- a master cannot have two overlapping appointments, except cancelled ones
EXCLUDE USING gist (master_id WITH =, tstzrange(starts_at, ends_at) WITH &&)
WHERE (status <> 'cancelled')
);
-- A foreign key does not index the referencing columns — add the indexes yourself
CREATE INDEX ON appointments (client_id);
CREATE INDEX ON appointments (master_id, service_id);
CREATE INDEX ON master_services (service_id);
CREATE INDEX ON clients (referred_by_id);-- Fictional salon data
INSERT INTO clients (name, phone, referred_by_id) VALUES
('Aigerim Seitova', '+7 700 000 0001', NULL),
('Madina Omarova', '+7 700 000 0002', 1),
('Daniyar Akhmetov', '+7 700 000 0003', NULL);
INSERT INTO masters (name) VALUES ('Aliya'), ('Saule');
INSERT INTO master_profiles (master_id, bio) VALUES
(1, 'Colorist'),
(2, 'Nail artist');
INSERT INTO services (name, duration_min, price) VALUES
('Haircut', 60, 8000.00),
('Coloring', 120, 25000.00),
('Manicure', 90, 7000.00);
INSERT INTO master_services (master_id, service_id) VALUES
(1, 1), (1, 2),
(2, 3);
INSERT INTO appointments (client_id, master_id, service_id, starts_at, ends_at, price) VALUES
(1, 1, 2, '2026-10-20 10:00+05', '2026-10-20 12:00+05', 25000.00),
(3, 1, 1, '2026-10-20 12:00+05', '2026-10-20 13:00+05', 8000.00),
(1, 2, 3, '2026-10-21 15:00+05', '2026-10-21 16:30+05', 7000.00);docker run -d --rm --name booking-db -e POSTGRES_PASSWORD=change-me-locally postgres:17-alpine
docker exec booking-db psql -U postgres \
-c "CREATE ROLE booking_app LOGIN PASSWORD 'change-me'" \
-c "CREATE DATABASE booking OWNER booking_app" \
-c "CREATE DATABASE shop_design OWNER booking_app"
docker cp db booking-db:/db
docker exec booking-db psql -U booking_app -d booking -v ON_ERROR_STOP=1 \
-f /db/booking.sql -f /db/booking-seed.sqlbooking=> \d appointments
Table "public.appointments"
Column | Type | Collation | Nullable | Default
------------+--------------------------+-----------+----------+------------------------------
id | bigint | | not null | generated always as identity
client_id | bigint | | not null |
master_id | bigint | | not null |
service_id | bigint | | not null |
starts_at | timestamp with time zone | | not null |
ends_at | timestamp with time zone | | not null |
price | numeric(10,2) | | not null |
status | text | | not null | 'booked'::text
Indexes:
"appointments_pkey" PRIMARY KEY, btree (id)
"appointments_client_id_idx" btree (client_id)
"appointments_master_id_service_id_idx" btree (master_id, service_id)
"appointments_master_id_tstzrange_excl" EXCLUDE USING gist (master_id WITH =, tstzrange(starts_at, ends_at) WITH &&) WHERE (status <> 'cancelled'::text)
Check constraints:
"appointments_check" CHECK (ends_at > starts_at)
"appointments_price_check" CHECK (price >= 0::numeric)
"appointments_status_check" CHECK (status = ANY (ARRAY['booked'::text, 'done'::text, 'cancelled'::text, 'no_show'::text]))
Foreign-key constraints:
"appointments_client_id_fkey" FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE RESTRICT
"appointments_master_id_service_id_fkey" FOREIGN KEY (master_id, service_id) REFERENCES master_services(master_id, service_id) ON DELETE RESTRICTLet’s test the rules on live data: a second profile for a master, booking a service the master does not perform, an overlapping time. The range tstzrange(starts_at, ends_at) is half-open by default — [): the start is included, the end is not. So a 13:00 appointment right after one ending at 13:00 is accepted, and a cancelled appointment stops occupying the slot thanks to WHERE (status <> 'cancelled').
booking=> INSERT INTO master_profiles (master_id, bio) VALUES (1, 'Second profile');
ERROR: duplicate key value violates unique constraint "master_profiles_pkey"
DETAIL: Key (master_id)=(1) already exists.
booking=> INSERT INTO appointments (client_id, master_id, service_id, starts_at, ends_at, price) VALUES (2, 2, 1, '2026-10-22 10:00+05', '2026-10-22 11:00+05', 8000.00);
ERROR: insert or update on table "appointments" violates foreign key constraint "appointments_master_id_service_id_fkey"
DETAIL: Key (master_id, service_id)=(2, 1) is not present in table "master_services".
booking=> INSERT INTO appointments (client_id, master_id, service_id, starts_at, ends_at, price) VALUES (2, 1, 1, '2026-10-20 11:30+05', '2026-10-20 12:30+05', 8000.00);
ERROR: conflicting key value violates exclusion constraint "appointments_master_id_tstzrange_excl"
DETAIL: Key (master_id, tstzrange(starts_at, ends_at))=(1, ["2026-10-20 06:30:00+00","2026-10-20 07:30:00+00")) conflicts with existing key (master_id, tstzrange(starts_at, ends_at))=(1, ["2026-10-20 05:00:00+00","2026-10-20 07:00:00+00")).
booking=> INSERT INTO appointments (client_id, master_id, service_id, starts_at, ends_at, price) VALUES (2, 1, 1, '2026-10-20 13:00+05', '2026-10-20 14:00+05', 8000.00);
INSERT 0 1
booking=> UPDATE appointments SET status = 'cancelled' WHERE id = 2;
UPDATE 1
booking=> INSERT INTO appointments (client_id, master_id, service_id, starts_at, ends_at, price) VALUES (2, 1, 1, '2026-10-20 12:00+05', '2026-10-20 13:00+05', 8000.00) RETURNING id;
id
----
7
(1 row)
INSERT 0 1Keys: surrogate or natural, bigint or UUID
A natural key is a real-world value that is already unique: an email, a phone number, a national ID. A surrogate key is a number the database issues that means nothing outside it. For the primary key you almost always take a surrogate and protect the natural one with a UNIQUE constraint, like the client’s phone in our schema.
Natural key (phone, email)
- It changes: a client gets a new number and you must change the key in every referencing table
- It can be empty or unknown, while a primary key must be
NOT NULL - It is often personal data — it spreads through foreign keys, logs and URLs
- Useful as
UNIQUE: stops the same client from being added twice
Surrogate key (bigint or uuid)
- Never changes and reveals nothing
- Compact: foreign keys and indexes on
biginttake 8 bytes per value - Does not prevent duplicates by itself — pair it with
UNIQUEon a natural attribute - In a junction table, the pair of foreign keys is the primary key — a separate
idis optional
bigint GENERATED ALWAYS AS IDENTITY
- Pros
- 8 bytes, numbers grow in order, easy to read and debug
- Cons
- The number reveals volumes and can be guessed; only this database can issue it
uuid version 4 (gen_random_uuid())
- Pros
- Can be created anywhere without asking the database; cannot be guessed
- Cons
- 16 bytes; random values land in different places of the index
uuid version 7 (uuidv7(), PostgreSQL 18)
- Pros
- Ordered by creation time, keeps the other UUID benefits
- Cons
- 16 bytes; the creation time can be extracted from the value
Foreign keys and ON DELETE behaviour
A foreign key prevents a reference from pointing at nothing. But what should happen when the referenced row is deleted? The ON DELETE rule decides, and you choose it by what the relationship means. The PostgreSQL docs put the principle this way: if the referencing row is a component of the whole and cannot exist on its own, CASCADE fits; if the objects are independent, use RESTRICT or NO ACTION.
NO ACTION (default)
- What happens
- Error if references remain; the check can be deferred to the end of the transaction
- Where in our schema
- —
RESTRICT
- What happens
- Error immediately, the check cannot be deferred
- Where in our schema
- Appointments → client and master–service pair: visit and revenue history
CASCADE
- What happens
- Dependent rows are deleted together with the parent
- Where in our schema
- Master profile, master–service links
SET NULL
- What happens
- The reference is set to null
- Where in our schema
referred_by_id: the referrer is optional information
SET DEFAULT
- What happens
- The reference gets its default value, which must also exist
- Where in our schema
- Rarely needed
booking=> DELETE FROM clients WHERE id = 1;
ERROR: update or delete on table "clients" violates foreign key constraint "appointments_client_id_fkey" on table "appointments"
DETAIL: Key (id)=(1) is still referenced from table "appointments".
booking=> DELETE FROM masters WHERE id = 1;
ERROR: update or delete on table "master_services" violates foreign key constraint "appointments_master_id_service_id_fkey" on table "appointments"
DETAIL: Key (master_id, service_id)=(1, 1) is still referenced from table "appointments".
booking=> INSERT INTO masters (name) VALUES ('Trainee') RETURNING id;
id
----
3
(1 row)
INSERT 0 1
booking=> INSERT INTO master_profiles (master_id, bio) VALUES (3, 'Junior');
INSERT 0 1
booking=> INSERT INTO master_services (master_id, service_id) VALUES (3, 1);
INSERT 0 1
booking=> DELETE FROM masters WHERE id = 3;
DELETE 1
booking=> SELECT count(*) AS profiles_left FROM master_profiles WHERE master_id = 3;
profiles_left
---------------
0
(1 row)
booking=> DELETE FROM appointments WHERE client_id = 1;
DELETE 2
booking=> DELETE FROM clients WHERE id = 1 RETURNING id, name;
id | name
----+-----------------
1 | Aigerim Seitova
(1 row)
DELETE 1
booking=> SELECT id, name, referred_by_id FROM clients ORDER BY id;
id | name | referred_by_id
----+------------------+----------------
2 | Madina Omarova |
3 | Daniyar Akhmetov |
(2 rows)Look at the second error: deleting the master cascaded into master_services and then hit the RESTRICT from appointments. Cascades and restrictions chain through the whole schema — one more reason to think them through up front. PostgreSQL 18 made this error more specific: it says the RESTRICT rule in particular was violated (violates RESTRICT setting of foreign key constraint).
In a real service you rarely need to delete clients: appointment history matters for reports and disputes. In our experience, instead of deleting a client you anonymise them — replace the name and phone, keep the row and its links. The RESTRICT on appointments.client_id is exactly what stops history from being wiped together with a client by accident.
Normalising to 3NF: an orders example
Normalisation means laying data out across tables so that every fact is stored in one place. Take a coffee shop’s order log kept in Excel: one row per order line, with the client and product details repeated in every row.
- 01Flat table
- 021NF: one value per cell
- 032NF: orders, order_items, products
- 043NF: clients split out
-- Orders the way they are kept in an Excel sheet: one row per line item
CREATE TABLE order_sheet (
order_no integer,
ordered_on date,
client_name text,
client_email text,
client_city text,
product text,
category text,
price numeric(10,2),
quantity integer
);
INSERT INTO order_sheet VALUES
(1, '2026-09-02', 'Aigerim Seitova', 'aigerim@example.com', 'Almaty', 'Espresso Blend 1 kg', 'coffee', 12900.00, 1),
(1, '2026-09-02', 'Aigerim Seitova', 'aigerim@example.com', 'Almaty', 'Paper filters V60', 'gear', 2500.00, 2),
(2, '2026-09-05', 'Daniyar Akhmetov', NULL, 'Astana', 'Ethiopia Yirgacheffe 250 g', 'coffee', 5600.00, 2),
(3, '2026-09-14', 'Aigerim Seitova', 'aigerim@example.com', 'Almaty', 'Ethiopia Yirgacheffe 250 g', 'coffee', 5600.00, 1);shop_design=> SELECT * FROM order_sheet;
order_no | ordered_on | client_name | client_email | client_city | product | category | price | quantity
----------+------------+------------------+---------------------+-------------+----------------------------+----------+----------+----------
1 | 2026-09-02 | Aigerim Seitova | aigerim@example.com | Almaty | Espresso Blend 1 kg | coffee | 12900.00 | 1
1 | 2026-09-02 | Aigerim Seitova | aigerim@example.com | Almaty | Paper filters V60 | gear | 2500.00 | 2
2 | 2026-09-05 | Daniyar Akhmetov | | Astana | Ethiopia Yirgacheffe 250 g | coffee | 5600.00 | 2
3 | 2026-09-14 | Aigerim Seitova | aigerim@example.com | Almaty | Ethiopia Yirgacheffe 250 g | coffee | 5600.00 | 1
(4 rows)
shop_design=> UPDATE order_sheet SET client_city = 'Astana' WHERE order_no = 3;
UPDATE 1
shop_design=> SELECT DISTINCT client_name, client_city FROM order_sheet ORDER BY 1, 2;
client_name | client_city
------------------+-------------
Aigerim Seitova | Almaty
Aigerim Seitova | Astana
Daniyar Akhmetov | Astana
(3 rows)That is an update anomaly. The same table hides two more: you cannot add a product until someone orders it (an insert anomaly), and deleting order 2 erases everything we knew about Daniyar (a delete anomaly). Normal forms remove them step by step:
1NF
- Rule
- One value per cell, no repeating groups of columns
- What we do with the table
- No “Espresso ×1, Filters ×2” in one cell and no
product1,product2columns: one line item, one row. Our table already complies
2NF
- Rule
- Every non-key column depends on the whole key, not on part of it
- What we do with the table
- The row key is
(order_no, product). Date and client depend onorder_noonly →orders; category and catalogue price depend on the product only →products; quantity and sale price depend on the pair →order_items
3NF
- Rule
- Non-key columns do not depend on each other
- What we do with the table
- In
orders, city and email depend on the client, not on the order number → aclientstable, the order keepsclient_id
-- 3NF: every fact is stored in one place
CREATE TABLE clients (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL,
city text,
email text UNIQUE
);
CREATE TABLE products (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL UNIQUE,
category text NOT NULL,
price numeric(10,2) NOT NULL CHECK (price > 0)
);
CREATE TABLE orders (
id bigint PRIMARY KEY,
client_id bigint NOT NULL REFERENCES clients (id),
ordered_on date NOT NULL
);
CREATE TABLE order_items (
order_id bigint NOT NULL REFERENCES orders (id) ON DELETE CASCADE,
product_id bigint NOT NULL REFERENCES products (id),
quantity integer NOT NULL CHECK (quantity > 0),
price numeric(10,2) NOT NULL CHECK (price > 0),
PRIMARY KEY (order_id, product_id)
);
-- Move the data out of the flat table
INSERT INTO clients (name, city, email)
SELECT DISTINCT client_name, client_city, client_email
FROM order_sheet
ORDER BY client_name;
INSERT INTO products (name, category, price)
SELECT DISTINCT product, category, price
FROM order_sheet
ORDER BY product;
INSERT INTO orders (id, client_id, ordered_on)
SELECT DISTINCT s.order_no, c.id, s.ordered_on
FROM order_sheet s
JOIN clients c ON c.name = s.client_name;
INSERT INTO order_items (order_id, product_id, quantity, price)
SELECT s.order_no, p.id, s.quantity, s.price
FROM order_sheet s
JOIN products p ON p.name = s.product;$ docker exec booking-db psql -U booking_app -d shop_design -v ON_ERROR_STOP=1 -1 -f /db/normalize.sql; echo "exit $?"
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
psql:/db/normalize.sql:34: ERROR: duplicate key value violates unique constraint "clients_email_key"
DETAIL: Key (email)=(aigerim@example.com) already exists.
exit 3
shop_design=> UPDATE order_sheet SET client_city = 'Astana' WHERE client_name = 'Aigerim Seitova';
UPDATE 3
$ docker exec booking-db psql -U booking_app -d shop_design -v ON_ERROR_STOP=1 -1 -f /db/normalize.sql; echo "exit $?"
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 2
INSERT 0 3
INSERT 0 3
INSERT 0 4
exit 0The load runs as a single transaction (the -1 flag), so the failed attempt left no half-filled tables. In this training example clients are matched by name; a real migration needs a reliable attribute — phone or email — and a manual check for duplicates.
SELECT o.id AS order_no, o.ordered_on, c.name AS client, c.city,
p.name AS product, i.quantity, i.price
FROM orders o
JOIN clients c ON c.id = o.client_id
JOIN order_items i ON i.order_id = o.id
JOIN products p ON p.id = i.product_id
ORDER BY o.id, p.name;
order_no | ordered_on | client | city | product | quantity | price
----------+------------+------------------+--------+----------------------------+----------+----------
1 | 2026-09-02 | Aigerim Seitova | Astana | Espresso Blend 1 kg | 1 | 12900.00
1 | 2026-09-02 | Aigerim Seitova | Astana | Paper filters V60 | 2 | 2500.00
2 | 2026-09-05 | Daniyar Akhmetov | Astana | Ethiopia Yirgacheffe 250 g | 2 | 5600.00
3 | 2026-09-14 | Aigerim Seitova | Astana | Ethiopia Yirgacheffe 250 g | 1 | 5600.00
(4 rows)
UPDATE clients SET city = 'Shymkent' WHERE email = 'aigerim@example.com';
UPDATE 1
INSERT INTO products (name, category, price) VALUES ('Kalita Wave 185', 'gear', 18500.00);
INSERT 0 1
DELETE FROM orders WHERE id = 2;
DELETE 1
SELECT name, city FROM clients ORDER BY id;
name | city
------------------+----------
Aigerim Seitova | Shymkent
Daniyar Akhmetov | Astana
(2 rows)When denormalisation is justified
Denormalisation means deliberately storing a copy or a derived value to speed up reads. Microsoft’s documentation on normalisation openly admits that third normal form is not always practical and advises that, if you break the rules, the application must anticipate the duplicates. The key rule: the copy needs one source of truth and a mechanism that keeps the two consistent.
Safe approaches
- A generated column: the database computes the value from the same row, and it cannot be written by hand
- A materialized view: a heavy report is stored as a table and refreshed with
REFRESH - A snapshot at the time of the event: price, address, order contents
Risky approaches
- A copy of the client name in every order “to avoid a JOIN”
- Counters and totals updated by application code — they drift apart on the first bug or race condition
- Denormalising “just in case”, without measuring a slow query
-- 1. The line total is computed by the database and cannot drift from quantity and price
ALTER TABLE order_items
ADD COLUMN line_total numeric(12,2) GENERATED ALWAYS AS (quantity * price) STORED;
-- 2. A ready-made report: stored like a table, refreshed on command
CREATE MATERIALIZED VIEW client_revenue AS
SELECT c.name, count(DISTINCT o.id) AS orders, sum(i.line_total) AS revenue
FROM clients c
JOIN orders o ON o.client_id = c.id
JOIN order_items i ON i.order_id = o.id
GROUP BY c.name;shop_design=> SELECT order_id, product_id, quantity, price, line_total FROM order_items ORDER BY 1, 2;
order_id | product_id | quantity | price | line_total
----------+------------+----------+----------+------------
1 | 1 | 1 | 12900.00 | 12900.00
1 | 3 | 2 | 2500.00 | 5000.00
3 | 2 | 1 | 5600.00 | 5600.00
(3 rows)
shop_design=> UPDATE order_items SET line_total = 0;
ERROR: column "line_total" can only be updated to DEFAULT
DETAIL: Column "line_total" is a generated column.
shop_design=> SELECT * FROM client_revenue ORDER BY name;
name | orders | revenue
-----------------+--------+----------
Aigerim Seitova | 2 | 23500.00
(1 row)
shop_design=> INSERT INTO orders (id, client_id, ordered_on) VALUES (4, 2, '2026-10-01');
INSERT 0 1
shop_design=> INSERT INTO order_items (order_id, product_id, quantity, price) VALUES (4, 1, 3, 12900.00);
INSERT 0 1
shop_design=> SELECT * FROM client_revenue ORDER BY name;
name | orders | revenue
-----------------+--------+----------
Aigerim Seitova | 2 | 23500.00
(1 row)
shop_design=> REFRESH MATERIALIZED VIEW client_revenue;
REFRESH MATERIALIZED VIEW
shop_design=> SELECT * FROM client_revenue ORDER BY name;
name | orders | revenue
------------------+--------+----------
Aigerim Seitova | 2 | 23500.00
Daniyar Akhmetov | 1 | 38700.00
(2 rows)After a new order the materialized view keeps showing the old figures until it is refreshed — the docs say plainly that its data is not always current. That is fine for a daily report and wrong for stock levels. PostgreSQL 17 supports only stored (STORED) generated columns; 18 adds virtual ones, computed on read and now the default. Before denormalising for speed, measure the query and try an index — that is what the indexes lesson covers.
Common design mistakes and exercises
A comma-separated list of ids in one cell ('1,4,7')
- Why it hurts
- No foreign keys, no JOIN, easy to store an id that does not exist
- What to do instead
- An N:M junction table
Columns phone1, phone2, phone3
- Why it hurts
- A repeating group: a fourth number means a schema change
- What to do instead
- A separate 1:N phones table
CASCADE on historical data
- Why it hurts
- Deleting a client wipes their orders and revenue from reports
- What to do instead
RESTRICTand anonymisation instead of deletion
Phone or email as the primary key
- Why it hurts
- Changing the value touches every referencing table
- What to do instead
- A surrogate key +
UNIQUEon the natural one
Status as free text
- Why it hurts
- The database fills up with
paid,PaidandPAID - What to do instead
CHECK (status IN (…))or a separate status table
A foreign key without an index
- Why it hurts
- Slow parent deletes and JOINs on large tables
- What to do instead
- An index on the referencing columns
How this was verified6 · passedchecked 11 October 2026
“Run” means the code was executed in a scratch project or container and the output in the article comes from that run. “Partial” means it was checked against stubs instead of external services. “Not run” means it needs a device, an account or a key; the steps were checked against the official documentation. The verification project files and run logs are kept in the InterLAB archive.