How to design a database that stays correct
A database is where an app keeps its information. If it is designed badly, data starts contradicting itself, gets lost or becomes slow to find. In this lesson you break a badly designed database on purpose, then fix it.
Relational design is about integrity first and performance second: normalise to remove anomalies, model cardinality explicitly, index for your access paths, and use transactions to keep invariants true under failures and concurrency.
From one big spreadsheet to proper tables
A small shop keeps every order in one big sheet. It looks fine, but the same information is copied again and again (the orange cells). Use the buttons to see what goes wrong, then split it into tables.
A single denormalised table with repeating facts. Trigger the three classic anomalies (update, delete, insert), then normalise to 3NF and repeat the same operations.
CREATE TABLE customers ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name text NOT NULL, email text NOT NULL UNIQUE ); CREATE TABLE products ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name text NOT NULL, price numeric(10,2) NOT NULL CHECK (price >= 0) ); CREATE TABLE orders ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, customer_id bigint NOT NULL REFERENCES customers(id), product_id bigint NOT NULL REFERENCES products(id), unit_price numeric(10,2) NOT NULL -- price at purchase time, on purpose );
customer_id, product_id). These pointers are called foreign keys, and each row's own unique number is its primary key.
- 1NF: atomic values with no repeating groups (no
products = "Mouse, Keyboard"). - 2NF / 3NF: every non-key column depends on the key, the whole key, and nothing but the key.
emaildepends on the customer, not on the order, so it moves out. - Deliberate denormalisation:
orders.unit_pricecopies the price on purpose. It records a historical fact (what was charged), not the current price. - Denormalise for reads only with a clear owner of truth (materialised views, caches, or columns maintained by triggers).
Relationships: one or many?
Things in a database are connected. The key question is always "how many of these go with how many of those?" Pick the answer, and the diagram shows how the tables connect.
Choose the cardinality, and the diagram shows where the foreign keys live and which constraints enforce it. Advanced mode adds self-referencing relationships.
(answer to see the schema)
Indexes: finding things fast
A table with a million users. Without an index, the database must look at every row. An index works like the alphabet tabs in a phone book: it jumps almost straight to the right place.
A sequential scan is O(n) over the heap. A B-tree index lookup is O(log n) page reads, with a fan-out of hundreds, so only 3–4 levels even for very large tables. Indexes cost write amplification and storage.
Without an index
With an index on email
Can the database use the index for this search?
Composite index: which predicates can use it?
There is an index on email. Decide for each search.
CREATE INDEX ON people (last_name, first_name); Think of it as sorted by last name, then first name inside each last name.
WHERE deleted_at IS NULL) and covering indexes (INCLUDE) where they help. Check plans with EXPLAIN (ANALYZE, BUFFERS), and drop indexes that nothing reads.Transactions: all or nothing
Alice sends Bob $100. That's two changes: take $100 from Alice, then give $100 to Bob. What if the server crashes between them?
A transfer is two writes that must preserve one invariant: the total balance stays constant. Inject a crash between the writes, with and without a transaction.
Concurrency: two withdrawals at once
Two sessions each withdraw $100 from Alice's $500 at the same moment. The correct result is $300.