20 problems where the obvious schema breaks.
Six tracks, in the order they are worth learning. Each one is a business scenario with rules that pull against each other just enough to matter, and the design that looks right on a diagram is usually the one that fails test four.
Fundamentals
04- natural-key-that-changesFree
The key the business insists on renaming
Warehouse codes look like perfect primary keys until operations renames one and every shipment points at nothing.
Difficulty 2 of 5 - types-that-refuse-bad-dataFree
Columns that refuse what should never be stored
A rating, an amount of money and a timestamp: three columns where the obvious type quietly accepts wrong data.
Difficulty 2 of 5 - one-primary-per-personSubscribers
Exactly one of them is the main one
A person keeps several addresses, at most one is primary, and a boolean column will not enforce that.
Difficulty 3 of 5 - a-tree-that-cannot-loopSubscribers
A category tree with no way back to the root
A parent_id column makes a tree, and nothing about it stops a category from becoming its own grandparent.
Difficulty 4 of 5
Deletion & History
04- soft-delete-without-breaking-signupsFree
Soft delete without breaking sign-ups
Keep deleted accounts on record forever, and still let someone sign up again with the same email.
Difficulty 3 of 5 - audit-trail-nobody-can-rewriteSubscribers
A record of who changed what, that nobody can change
Log every salary change automatically, and make the log itself refuse to be edited or deleted.
Difficulty 4 of 5 - one-published-version-at-a-timeSubscribers
Every draft kept, exactly one of them live
Documents keep every revision, version numbers restart at 1 for each document, and only one version is ever published.
Difficulty 4 of 5 - a-balance-you-cannot-forgeSubscribers
A balance that cannot be wrong
Money moves between accounts, never appears, and the balance is something you derive rather than something you set.
Difficulty 5 of 5
Multi-tenancy
03- multi-tenant-unique-constraintFree
Uniqueness that stops at the tenant boundary
The same email has to be free to join two different companies, and blocked from joining one of them twice.
Difficulty 2 of 5 - tenant-isolation-with-rlsSubscribers
Isolation that survives a forgotten WHERE clause
One table holds every tenant’s invoices, and a query with no tenant filter must still see only its own.
Difficulty 5 of 5 - invoice-numbers-that-restartSubscribers
Invoice numbers that start at 1 for every customer
Each tenant numbers its own invoices from 1, with no gaps, and a database sequence can do neither.
Difficulty 4 of 5
Time
03- price-that-changes-over-timeFree
A price that changes without erasing what it was
Answer what a product cost on any past date, instead of losing that answer every time the price is updated.
Difficulty 3 of 5 - same-day-in-every-timezoneSubscribers
The day it happened depends on where you are standing
A booking has an instant and a local day, and storing both as the same type gets one of them wrong.
Difficulty 4 of 5 - no-overlapping-assignmentsSubscribers
Two people cannot hold the same desk at once
Assignments have a start and an end, and no two for the same desk may overlap in time.
Difficulty 5 of 5
Normalization
03- tags-are-not-a-stringFree
Tags that a search can actually trust
A comma-separated column looks like tags until searching for "sql" also returns everything tagged "nosql".
Difficulty 3 of 5 - order-lines-keep-their-priceFree
The invoice that must not change when the price does
A shop raises a price and last month’s orders have to stay exactly what the customer was charged.
Difficulty 4 of 5 - copy-the-fact-point-at-the-entitySubscribers
One column must be copied, the other must not
A shipment freezes the address it went to and follows the customer’s email wherever it changes: same table, opposite rules.
Difficulty 4 of 5
Performance
03- index-the-query-not-the-columnFree
An index serves a question, not a column
Indexing each column separately feels thorough and still leaves the one query that matters sorting the whole table.
Difficulty 3 of 5 - the-index-the-query-cannot-useSubscribers
The column is indexed and the query still scans
Wrapping a column in lower() hides it from its own index, and the plan looks the same as having no index at all.
Difficulty 4 of 5 - a-count-you-can-affordSubscribers
A number that is right and cheap at the same time
The comment count on every thread, without counting comments, and still correct after a delete.
Difficulty 5 of 5