Models for transactions, and models for questions

Why this matters

Somebody in every company has asked, at some point, why a simple question takes three days to answer. The data is right there. The order system knows every order. And yet "what did we sell by region last quarter, compared with the quarter before, split by whether the customer had complained" turns into a ticket, a meeting, and a spreadsheet that nobody trusts.

The reason is almost never laziness or a missing index. It is that the database holding those orders was designed to *record* orders quickly and correctly, one at a time, and a database designed for that job is the wrong shape for reading millions of rows and slicing them six ways. Both designs are right. They are right for different jobs.

This course is about the second shape: how to design the tables a business reports from, on PostgreSQL 16, and how to build them from the tables a business runs on. You will do it on ultishop, the order database of a fictional online retailer, which you may recognise from ULC-002. This time you are not querying it. You are the person who has to give the sales, support and finance teams something better to query.

The first lesson is about looking before designing. You will read the source system properly, write down what it holds and what it cannot answer, and set up the workspace where your model will live.

Concepts

A transactional model — the shape of ultishop — is optimised for writing. It holds each fact once, in exactly one place, so that changing it means changing one row. That property is what normalisation buys, and it is described in a ladder of normal forms.

First normal form (1NF) means every column holds a single value: no comma-separated lists, no phone1, phone2, phone3. Second normal form (2NF) means every non-key column depends on the *whole* key, not part of it. Third normal form (3NF) means no non-key column depends on another non-key column: if a table has category_id and category_name, the name depends on the id rather than on the table's own key, so it belongs in a categories table. Most well-built operational schemas stop at 3NF, and so should you. The higher forms (BCNF, 4NF, 5NF) exist and are occasionally useful, but past 3NF the cost in joins usually outruns the benefit in integrity.

Normalisation has a price, and the price is joins. Answering a reporting question on a normalised schema means assembling it from six or eight tables every time, with the join logic re-derived — often slightly differently — by every person who writes a query. It is also *fragile in time*: a normalised customer table holds the customer as they are now. Overwrite a region when someone moves and every historical report quietly changes.

An analytical model is optimised for reading and for being understood. It accepts deliberate redundancy in exchange for fewer joins, stable definitions and the ability to keep history. That is the dimensional model, and the next lesson builds one.

Before either, it helps to separate three levels of description. The conceptual model names the things the business cares about and how they relate: customers place orders; orders contain products. The logical model adds attributes, keys and cardinalities without naming a product: one customer has many orders; an order has at least one line. The physical model is the DDL: data types, constraints, indexes, partitions, the things that differ between PostgreSQL and anything else. Most arguments about modelling are really arguments happening at different levels, and naming the level ends most of them.

One more habit, and it is the most important one in this lesson: you never write to the source system. The order database belongs to the people who run orders. A warehouse reads it, on a schedule, through an account that cannot do anything else. In the lab you will read ultishop as the read-only analyst role and build everything of your own in a database you create. A check at the end of this lesson verifies the source is untouched, and it stays in force for the whole course.

Guided exercise

You are working on db01, signed in as analyst, with the password already in ~/.pgpass so psql never asks for it.

1. Confirm the database server is running and look at the source system. The cluster unit on Ubuntu is postgresql@16-main; the plain postgresql unit is only an umbrella that reports active even when the cluster is down.

``bash systemctl is-active postgresql@16-main psql -h localhost -U analyst -d ultishop -c "\dt shop.*"

1. Read the relationships out of the catalog rather than guessing them. Every foreign key the source declares is in pg_constraint:

``sql SELECT c.conrelid::regclass AS child, a.attname AS column_name, c.confrelid::regclass AS parent FROM pg_constraint c JOIN unnest(c.conkey) k(att) ON true JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = k.att WHERE c.contype = 'f' AND c.connamespace = 'shop'::regnamespace ORDER BY 1, 2;

Nine foreign keys come back. Note which ones are optional (the column is nullable) — that is where the model will later need an unknown member.

1. Profile the columns that will become dimension attributes. Two queries are enough to find the trouble:

``sql SELECT lower(status) AS conformed, count(*) FROM shop.orders GROUP BY 1 ORDER BY 2 DESC; SELECT count(*) AS impossible_dates FROM shop.orders WHERE order_date < '2024-01-01' OR order_date >= '2027-01-01';

```text conformed | count -----------+------- delivered | 10330 cancelled | 702 refunded | 474 placed | 327 paid | 90 shipped | 77

impossible_dates ------------------ 8 ```

Six real statuses arrive as sixteen different strings, because status is free text. Eight orders are dated 1970-01-01 or 2099-12-31. Both are ordinary; a source system that has never been read for reporting always looks like this.

1. Create the database your warehouse will live in. The analyst role has CREATEDB, so the warehouse is yours and the source stays somebody else's:

``bash createdb -h localhost -U analyst warehouse

1. Build the landing area — one table per source table, mirroring its columns and taking whatever the source sends. Landing tables carry no primary keys, no foreign keys and no check constraints, and that is deliberate: a landing table that rejects a bad row hides the bad row.

Your lab has no internet access, by design, so every file this course asks you to create is printed in the lesson that uses it. Open an editor on db01 (nano ~/warehouse/sql/01-landing-schema.sql), paste this in, and save:

``sql \set ON_ERROR_STOP on DROP SCHEMA IF EXISTS landing CASCADE; CREATE SCHEMA landing; CREATE TABLE landing.regions (region_id smallint, region_code text, region_name text, shipping_zone smallint); CREATE TABLE landing.categories (category_id smallint, category_name text, department text); CREATE TABLE landing.products (product_id integer, sku text, product_name text, category_id smallint, unit_price numeric(10,2), cost_price numeric(10,2), introduced_on date, discontinued_on date, active boolean); CREATE TABLE landing.customers (customer_id integer, first_name text, last_name text, email text, region_id smallint, signup_date date, marketing_opt_in boolean); CREATE TABLE landing.orders (order_id integer, customer_id integer, order_date timestamp, status text, channel text, ship_region_id smallint, promo_code text, shipping_fee numeric(8,2)); CREATE TABLE landing.order_items (order_item_id integer, order_id integer, product_id integer, quantity integer, unit_price numeric(10,2), discount_pct numeric(5,2)); CREATE TABLE landing.payments (payment_id integer, order_id integer, paid_at timestamp, method text, amount numeric(10,2), status text); CREATE TABLE landing.support_tickets (ticket_id integer, customer_id integer, order_id integer, opened_at timestamp, closed_at timestamp, category text, priority text, subject text, satisfaction_score smallint); COMMENT ON SCHEMA landing IS 'Raw extracts from the Ultishop order system, one table per source table, unconstrained and untransformed.';

Then apply it:

``bash psql -h localhost -U analyst -d warehouse -v ON_ERROR_STOP=1 -f ~/warehouse/sql/01-landing-schema.sql

1. Land the data. Two psql sessions joined by a pipe: the left one reads the source as the read-only analyst role and writes CSV to standard output, the right one loads it. Nothing is written to disk and nothing is written to the source. Save this as ~/warehouse/sql/02-land-sources.sh and run it:

``bash for t in regions categories products customers orders order_items payments support_tickets; do psql -X -q -h localhost -U analyst -d ultishop -c "\copy (SELECT * FROM shop.$t) TO STDOUT (FORMAT csv)" \ | psql -X -q -h localhost -U analyst -d warehouse -c "\copy landing.$t FROM STDIN (FORMAT csv)" done

``text regions 6 rows categories 8 rows products 120 rows customers 2000 rows orders 12000 rows order_items 25679 rows payments 11696 rows support_tickets 1500 rows

1. Write the source model note at ~/warehouse/design/source-model.md. This is the document a warehouse project actually starts with, and it has three sections: an Entities table (one row per source table, with its grain in words, its primary key and its row count), a Relationships list naming every foreign key in child.column -> parent.column form with the cardinality and whether it is optional, and a Normalisation defects list. Add a closing section on what the source *cannot* answer.

Your defects list should include at least: orders.status as unconstrained free text; customers keeping no history, so a region change rewrites the past; and order_date having no range check. Also record one thing that looks like a defect and is not — order_items.unit_price duplicating products.unit_price is a correct denormalisation, because the price charged must not move when the catalogue price moves — so that nobody "fixes" it later.

Troubleshooting

`createdb: error: ... permission denied to create database` → the analyst role lost its CREATEDB attribute, or you are connected as a different role. Check with psql -h localhost -U analyst -d ultishop -c "SELECT current_user, rolcreatedb FROM pg_roles WHERE rolname = current_user;". On a fresh lab it is granted by the pod setup.

`psql: error: connection to server ... failed: FATAL: password authentication failed` → ~/.pgpass has been changed or its permissions are no longer 0600. libpq silently ignores the file if it is group- or world-readable. chmod 0600 ~/.pgpass and try again; the password is also shown on your lab card.

`ERROR: permission denied for schema shop` → you are connected to warehouse, not ultishop. The two databases are separate; a query cannot see across them. Check with \conninfo.

The `\copy` pipe loads zero rows and reports no error → the left-hand psql printed a notice or a banner into the pipe. Keep -X -q on both sides (-X ignores ~/.psqlrc, -q suppresses chatter) and make sure the \copy on the left ends with TO STDOUT, not a file name.

A landing table has more rows than the source → you ran the load twice without recreating the schema. Landing has no primary key to stop you, on purpose. Re-run 01-landing-schema.sql, which drops and rebuilds the schema, then load again.

Check your understanding

Summary and next step

• A transactional model holds each fact once and is optimised for writing; an analytical model accepts

redundancy to be read and understood. Neither is the "correct" one.

• Normalise operational data to 3NF and stop; past that the joins cost more than the integrity buys.
• Read the source from the catalog before designing anything, write down what it cannot answer, and never

write to it.

Next: the dimensional model itself, and the four steps that produce one.

References

• PostgreSQL 16 Documentation — https://www.postgresql.org/docs/16/index.html (accessed 2026-09-19)
• PostgreSQL 16: CREATE DATABASE — https://www.postgresql.org/docs/16/sql-createdatabase.html (accessed 2026-09-19)
• PostgreSQL 16: pg_constraint — https://www.postgresql.org/docs/16/catalog-pg-constraint.html (accessed 2026-09-19)
• PostgreSQL 16: psql (\copy) — https://www.postgresql.org/docs/16/app-psql.html (accessed 2026-09-19)
• Kimball Group, Dimensional Modeling Techniques — https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/ (accessed 2026-09-19)