Anomalies

Storing one fact in many rows lets the copies disagree

orders.xlsx: everything in one sheetorderdatecustomercityproductprice1012025-10-01BenLeedsPen1.201022025-10-01AshaPuneInk6.501032025-10-02BenLeedsNotebook3.001042025-10-04ChenPerthStapler8.751052025-10-05BenYorkPen1.201062025-10-06AshaPunePen1.20Update anomalyBen moved. His city is stored in 3 rows; onewas changed, so the sheet now holds 2 cities for him.Insertion anomalyA new product, Glue at 2.40, cannot be added untilsomebody orders it: a row needs an order number.Deletion anomalyCancel order 104 and the only record that a Staplercosts 8.75, and that Chen lives in Perth, goes with it.Storing one fact in many rows lets the copies disagree, and ties unrelated facts together.The pen’s price is written 3 times and Ben’s city 3 times. Every copy is one more place an update can miss. The cureis to give each kind of thing its own table and store each fact once. That process is called normalisation, and its result ison the next page.

A wide sheet repeats facts, so it suffers update, insertion and deletion anomalies. Ben’s city appears 3 times.

Normalisation

Give each kind of thing its own table, and point at it by key

customersidnamecity1BenLeeds2AshaPune3ChenPerthproductsidnameprice1Pen1.202Ink6.503Notebook3.004Stapler8.75ordersorderdatecustomer_idproduct_id1012025-10-01111022025-10-01221032025-10-02131042025-10-04341052025-10-05111062025-10-0621customer_id points at one rowproduct_id points at one rowNormalised, each fact is stored once and every other table refers to it by key.Ben’s city is now one cell; change it there and every order sees it. The new product can be added on its own. Cancelling an ordertouches only that order. The sheet held 36 cells; the three tables hold 45, and the gap widens with every order. A query joinsthem back together whenever the wide view is wanted (lesson 3).

Normalisation splits the sheet so each fact is stored once; foreign keys point at the row that holds it.

Schema migrations · Going deeper

Change a live table in small steps that old and new code both survive

A schema change takes a lock. Expand, migrate, contract splits it into steps that never block or break running code.

Why ALTER TABLE hurts on a busy table

Most ALTER TABLE forms take a lock that blocks every read and write until they finish. Worse, the ALTER queues behind any open transaction, and every new query then queues behind the ALTER.

InstantADD COLUMN, with or without a constant DEFAULT (PostgreSQL 11 and later); DROP COLUMN; renaming. Only the catalogue changes.
Rewrites or scanschanging a column’s type; adding NOT NULL or a CHECK or FOREIGN KEY, which must read every row to validate it.
Blocks writesa plain CREATE INDEX, for as long as the build takes.

The safe forms, in PostgreSQL

  • SET lock_timeout = '2s' so an ALTER that cannot get its lock gives up instead of stalling the application. Retry it.
  • CREATE INDEX CONCURRENTLY builds without blocking writes. It cannot run inside a transaction block, and a failed build leaves an invalid index to drop.
  • ADD CONSTRAINT … NOT VALID, then VALIDATE CONSTRAINT the first step is instant and applies to new rows; the second checks old rows without blocking writes.

Expand, migrate, contract: renaming a column with no downtime

  1. Expand add the new column. Deploy code that writes both and still reads the old one.
  2. Migrate backfill old rows in batches of a few thousand, each its own transaction, so no lock is held for long.
  3. Contract switch reads to the new column, stop writing the old one, and only then drop it.
Every step is a migration file in version control, applied in order by a tool such as Flyway, Liquibase, Alembic or EF Core migrations. The database records which have run, so every environment reaches the same schema.

Normal forms

Each form removes one more way to store a fact twice

First normal formone value in each cellorderproducts101Pen, Ink102Notebooktwo products crammed into one cell✓ one row per productSecond normal formdepend on the whole keyorderproduct_idqtyname10112Pen10121Ink10315Penname depends on product_id alone✓ name moves to productsThird normal formand nothing but the keyidpostcodetown1LS1 4APLeeds2LS1 4APLeedstown depends on postcode, not id✓ a postcodes tableEach normal form removes one way a fact can be stored twice: in one cell, against part of a key, or behind another column.The rule of thumb for third normal form: every column depends on the key, the whole key, and nothing but the key. Higher forms(Boyce-Codd, fourth, fifth) exist but rarely matter in practice. Most transaction databases are designed to third normal form;analytical models deliberately are not (lesson 8).

1NF: one value per cell. 2NF: depend on the whole key. 3NF: depend on nothing but the key.

Normal forms · Going deeper

Denormalising on purpose

Normalise for writes; denormalise for reads, when the copies can be kept in step by the system rather than by people.

Normalised

  • Each fact once an update touches one row, so copies cannot disagree.
  • Small rows more fit in memory; writes are fast.
  • Joins to read a screen showing an order with its customer and products reads four tables.

Denormalised

  • Facts copied the customer’s name stored on each order, or a running total kept on the customer row.
  • Fast reads one table, no joins.
  • Risk every copy must be updated, by a trigger, a job or the application.

Where denormalising is normal

  • Analytics star schemas copy descriptive attributes into wide dimension tables because analysts read far more than anyone writes (lesson 8).
  • NoSQL a document or key-value store has no joins, so data is shaped to match each query, copies and all (lesson 7).
  • History order_items keeps the unit_price paid. That looks like a copy of the product’s price but is a different fact: the price on that day.
Design normalised first. Denormalise one measured hot path at a time, and write down which process keeps the copy correct.

Database objects

Views, indexes and code live beside the data

Database: shopone server can hold many databasesSchema: salesSchema: reportingTablescustomers, orders: the dataIndexessorted copies of a column, for speedStored proceduresplace_order(): code run inside the databaseViewsa saved query that looks like a tableTriggerscode that runs when a row changesFunctionsorder_total(id): returns a valueA database holds more than tables: views, indexes and code live beside the data, grouped into schemas.A schema is a namespace and a unit of permission: grant analysts the reporting schema and nothing in sales. A view stores aquery, not data, so it always shows current rows and can hide columns. Indexes are lesson 6. Procedures and triggers keeprules next to the data; use them sparingly, because logic hidden in the database is easy to forget.

A database holds tables, views, indexes, stored procedures, functions and triggers, grouped into schemas.

Database objects · Going deeper · 1 of 2

Who works on the data

A database administrator keeps databases running, a data engineer moves and shapes data, a data analyst turns it into answers.

The three roles DP-900 names

  • Database administrator (DBA) installs, configures and patches database systems; manages security, users and permissions; plans backups, restores and high availability; tunes performance.
  • Data engineer builds and runs pipelines that ingest data from operational systems, clean and transform it, and load it into warehouses and lakes; owns data quality, privacy rules in the pipeline, and the platforms analysts use.
  • Data analyst explores data to find trends, builds models, reports and dashboards (often in Power BI or similar), and explains the answers to the business.

And beside them

Analytics engineermodels data in SQL with tools such as dbt, between engineer and analyst
Data scientistbuilds statistical and machine learning models
Data stewardowns definitions, quality rules and access for a data domain
In a managed cloud database much of the DBA’s routine work (patching, backups, failover) is done by the provider; the role shifts to design, security and performance.

Functions, procedures, triggers · Going deeper · 2 of 2

A function computes, a procedure acts, a trigger fires by itself

Function: returns a value, used inside a queryCREATE FUNCTION with_vat(p numeric) RETURNS numeric LANGUAGE sql IMMUTABLE AS $$ SELECT round(p * 1.20, 2) $$;SELECT name, with_vat(price) FROM products;→ Pen 1.44 · Ink 7.80Procedure: run with CALL, may COMMIT insideCREATE PROCEDURE raise_prices(pct numeric) LANGUAGE plpgsql AS $$ BEGIN UPDATE products SET price = round(price * (1 + pct/100), 2); COMMIT; END $$;CALL raise_prices(10);Trigger: runs by itself when a row changesCREATE TRIGGER price_changed AFTER UPDATE OF price ON products FOR EACH ROW WHEN (OLD.price IS DISTINCT FROM NEW.price) EXECUTE FUNCTION log_price();log_price() inserts OLD.price and NEW.price into price_audit.After CALL raise_prices(10), nobody having asked for it:price_auditproduct_idold_pricenew_pricechanged_by11.201.32postgres26.507.15postgresSELECT raise_prices(10);ERROR: raise_prices(integer) is a procedureA function computes, a procedure acts, and a trigger fires on its own, which is what surprises people.A function is called from SQL and should not change data; a procedure is called on purpose and can control transactions; atrigger runs inside whatever statement touched the table, on every path, including bulk loads, where a row-by-row trigger isslow. Run on PostgreSQL 16.

A function returns a value inside a query; a stored procedure is run with CALL; a trigger runs whenever a row changes.

Lesson complete

You finished “Designing tables”

Nicely done.

Open in a new tab