← back to the section

The task sounds simple: check that cancelling an order creates a refund. You open the database on the test stand — forty tables with names like order_items and idempotency_keys, and no idea where to look. Your bearings come from the schema: which tables exist, what columns and types they hold, what is mandatory, which table points at which. Developers write it, everybody reads it — it shows where your data lives, which field cannot be empty, what disappears with an order.

an order holds many products, a product sits in many orders ordersidstatusord-07PAIDord-21NEW productsidtitleprd-01mouseprd-03desk ?a column holds one value —there is nowhere to write «many products» order_items — the joining tableorder_idproduct_idqtyord-07prd-012ord-07prd-031ord-21prd-015 order_idproduct_idtwo one-to-many links instead of one many-to-many

Two tables cannot record a many-to-many link: a column holds a single value. So a third table appears between them with a pair of keys — and with a column of its own, quantity, that belongs to the pair rather than to the order or to the product.

A schema is rules, not data

A table is not drawn with a mouse: it is declared by a query, and that query stays its description — any client shows it on a DDL tab. This is orders in the course sandbox:

CREATE TABLE orders (
    id            VARCHAR(36) PRIMARY KEY,
    customer_id   VARCHAR(36) NOT NULL,
    seller_id     VARCHAR(36) NOT NULL,
    status        VARCHAR(32) NOT NULL,
    currency      VARCHAR(3) NOT NULL,
    total_amount  NUMERIC(15,2) NOT NULL,
    created_at    TIMESTAMP NOT NULL,
    paid_at       TIMESTAMP
);

Read it line by line: name, type, constraints. NOT NULL makes a field mandatory — a row without it is refused. paid_at has no such constraint, which is a hint: an order with no payment date exists, and that state has to be checked.

ALTER TABLE orders ADD COLUMN cancelled_at TIMESTAMP; changes an existing table: a column added, a constraint added or dropped. DROP TABLE orders; removes it with its rows, no questions and no recycle bin — only a backup brings it back. We read these commands rather than run them: the stand schema belongs to developers.

Keys: primary, surrogate, foreign

A row needs a handle. A primary key is a column whose value is unique and never empty, so it identifies a row unambiguously. Almost always it is a meaningless id — a surrogate key, a UUID or a number the database issues itself. An email is tempting instead, but emails change, and other tables already point at the key.

A foreign key is a promise that the value in a column exists in another table:

ALTER TABLE payments
    ADD CONSTRAINT fk_payments_order
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE;

Two things follow. A payment with a non-existent order_id never reaches the database — it is refused there, even when the application asks. And the ON DELETE tail decides the fate of related rows: with CASCADE an order is deleted and its payments and items vanish quietly, with RESTRICT the database refuses to delete it while anything points at it. This is where the defect "we deleted a product and the items disappeared from old orders" is born.

Where the third table comes from

An order holds many products, and one product sits in many orders — two tables cannot record that. So a third, joining table appears: in the sandbox that is order_items, where a row means "order ord-07 holds product prd-01, two of them".

The pair's own fields live there too: quantity and unit_price, the price at the moment of purchase. A price rise therefore does not rewrite history — change the price in products and the old order_items must stay as they were.

The total then lives in two places, orders.total_amount and the items. One query finds the discrepancy; an empty result means it adds up:

live example

SELECT o.id, o.total_amount, sum(i.unit_price * i.quantity) AS items_total
FROM orders o
JOIN order_items i ON i.order_id = o.id
GROUP BY o.id, o.total_amount
HAVING sum(i.unit_price * i.quantity) <> o.total_amount;
Run

Running examples is part of paid access. There the same code runs inside the article: editor, run and check next to the paragraph. Free week →

Data types, and CHAR against VARCHAR

A column type is a boundary someone chose before you, and it is yours to test. VARCHAR(255) is a ready pair of checks at 255 and 256 characters, NUMERIC(15,2) raises the question of a third decimal digit, INT stops the counter at 2,147,483,647. Money is kept in NUMERIC, not FLOAT: floating types store approximations, and on cents that shows up in totals.

Of the two string types, VARCHAR(3) stores exactly what was written — RUB is three characters — while CHAR(3) keeps a fixed length and pads shorter values with spaces: RU becomes RU␣. Exports then carry trailing spaces, and a comparison with 'RU' behaves differently across databases. Hence VARCHAR by default, and CHAR only for strictly fixed-length fields: a currency or country code.

A view: a query that got a name

"Take a look at the paid orders view" sounds as if such a table existed, and the schema has none. Most likely this is a view — a query saved under a name: you read it like a table, but it stores no data, and every read runs the SELECT inside.

CREATE VIEW paid_orders AS
SELECT id, customer_id, total_amount, paid_at
FROM orders
WHERE status = 'PAID';

SELECT * FROM paid_orders WHERE paid_at >= DATE '2026-01-01';

Views exist for two reasons. A join across six tables is written once and read by everyone, so reports stop disagreeing about what counts as paid. The second is access: you grant the view, which has no email column, instead of the table.

A view always returns fresh data — unlike a materialized one, which stores a computed result and refreshes on a schedule. When something "is in the database but not in the view", ask when it was last refreshed: an hour behind can be the design, not a defect.

ACID: why a payment does not hang halfway

A payment is not one action: a row in payments, a status change on the order, an event written down. Let the process die in the middle and the order is paid in money but not in status. A transaction protects against that, and its guarantees have four names.

Atomicity — the steps apply all or none, never halfway. Consistency — only a state that breaks no rule of the schema is committed: a payment against a non-existent order fails on the foreign key. Isolation — parallel transactions do not see each other's half-done changes: two buyers of the last item queue up instead of both taking it. Durability — after COMMIT the data survives a service restart and a machine going down.

The guarantees end at the database. A step that left for an external payment service cannot be rolled back — the money is there, the row is missing here. Hence the checks: the payment went through, the answer never arrived, the client repeated the request. In the sandbox idempotency_keys hints at that — it exists so a repeat does not create a second order.

In short

  • A schema is rules, not data: CREATE TABLE declares columns and constraints, ALTER changes them, DROP removes the table with its rows.
  • NOT NULL marks mandatory fields, the type sets the boundaries to test: money is NUMERIC, not FLOAT; CHAR pads values with spaces.
  • Primary keys are surrogate — they never change because they mean nothing; ON DELETE says what vanishes with the parent row.
  • A many-to-many link lives in a third table, with the fields of the pair: quantity and the price at the moment of purchase.
  • A view always returns fresh data; a materialized one stores the result and lags until the next refresh.
  • ACID holds inside the database; a step into an external service cannot be rolled back, so there you check repeats.