Anomalies
Storing one fact in many rows lets the copies disagree
A wide sheet repeats facts, so it suffers update, insertion and deletion anomalies. Ben’s city appears 3 times.
Here is how most data starts life: one sheet, one row per order line, every detail typed in. It works until something changes. Ben moves house. His city is stored in 3 rows, someone updates one, and the sheet now disagrees with itself. A new product arrives. It cannot be listed until someone orders it, because every row needs an order number. Cancel order one-oh-four, and the only record of the stapler's price, and of where Chen lives, goes with it. Three different failures, one cause: facts stored more than once, tied to facts they have nothing to do with.
Normalisation
Give each kind of thing its own table, and point at it by key
Normalisation splits the sheet so each fact is stored once; foreign keys point at the row that holds it.
The fix is to give each kind of thing its own table. Customers, 3 of them, each with a city written once. Products, 4 of them, each with one price. And orders, which now hold no names or prices at all, only numbers. Customer id one points at Ben's row. Product id points at the product. Change Ben's city in one cell and every order sees it at once.
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.
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. 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. Expand add the new column. Deploy code that writes both and still reads the old one. Migrate backfill old rows in batches of a few thousand, each its own transaction, so no lock is held for long. 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.
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.
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
- Expand add the new column. Deploy code that writes both and still reads the old one.
- Migrate backfill old rows in batches of a few thousand, each its own transaction, so no lock is held for long.
- Contract switch reads to the new column, stop writing the old one, and only then drop it.
Normal forms
Each form removes one more way to store a fact twice
1NF: one value per cell. 2NF: depend on the whole key. 3NF: depend on nothing but the key.
The normal forms are a checklist for finding repeated facts. First normal form: one value in each cell. A cell holding pen comma ink breaks it. Give each product its own row. Second normal form applies when the key has two parts. Here the key is order and product id, but the product name depends on the product id alone, so it repeats on every order for a pen. Move it to products. Third normal form: nothing but the key. The town depends on the postcode, not on the customer, so every customer in one postcode repeats it. The well-known summary: the key, the whole key, and 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.
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.
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.
Database objects
Views, indexes and code live beside the data
A database holds tables, views, indexes, stored procedures, functions and triggers, grouped into schemas.
A database is more than its tables. Objects are grouped into schemas, which are namespaces and units of permission. In sales there are the tables themselves, indexes that make lookups fast, and stored procedures: code that runs inside the database, such as place order. In reporting there are views, saved queries that look like tables and can hide columns; triggers, code that runs when a row changes; and functions that return a value.
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.
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. 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.
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
Functions, procedures, triggers · Going deeper · 2 of 2
A function computes, a procedure acts, a trigger fires by itself
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.