Data split across tables and stitched back together by keys. Click a row to follow its relationships, then flatten everything into one table to see exactly what normalisation is protecting you from.
Fifty years on, the relational model is still the default way to store business data. This is why.
A relational database stores data in tables of rows and columns, where every table describes one kind of thing — customers, orders, products — and tables are connected by shared key values rather than by nesting data inside each other.
| Key | What it does |
|---|---|
| Primary key | Identifies a row uniquely; never NULL |
| Foreign key | Points at another table's primary key |
| Composite key | A primary key made of several columns |
| Candidate key | Any column set that could serve as primary |
| Surrogate key | An artificial id (integer or UUID) with no business meaning |
| Natural key | A real-world identifier, such as an ISBN or email |
The foreign key is what turns a pile of tables into a database. Declared properly, it makes an order pointing at a non-existent customer impossible — not unlikely, not caught by a validation function somewhere, but rejected by the engine for every client that ever connects.
Surrogate versus natural keys is one of the few genuinely contested design questions. Surrogate keys are stable (people change email addresses) and compact, so most schemas use them. The right answer is usually both: a surrogate primary key for references, plus a UNIQUE constraint on the natural key so the real-world rule is still enforced.
Switch this lab to one flat table and look at the red cells. The customer's name and city now repeat on every order they ever placed. That redundancy causes three classic problems:
The relational model describes how data is arranged. ACID describes what happens when several people change it at once, and it is the reason banks and hospitals run on these systems.
Together those guarantees mean the application does not have to reason about concurrency in the general case. Without them, every developer touching the system would have to get locking right, every time, for ever.
A relational database stores data in tables: rows are records, columns are attributes, and every row in a table has the same columns. That much is just a spreadsheet.
What makes it relational is that tables refer to each other by value rather than by pointer. An order does not contain a customer; it contains a customer id, and the database can follow that id to the customers table whenever asked. Nothing is nested, nothing is duplicated, and the connections are made at query time.
That design decision, from Edgar Codd's 1970 paper, is why the model has outlasted everything built to replace it. Because relationships are values rather than structure, you can ask questions nobody anticipated when the schema was designed — join any two tables on any matching columns, and the answer comes out. A system that stored orders inside customers would answer "orders per customer" instantly and "customers per product" only with a full rewrite.
Splitting has an obvious cost: getting a customer's name onto their order list requires a join. The benefits are less obvious until something goes wrong without them.
Store the customer's city on every order and one customer moving house means updating hundreds of rows. Miss one and the database now contains two contradictory answers to the same question, with no way to tell which is right.
Store it once, in the customers table, and the update touches a single row. Everything that references that customer sees the new value immediately, because there is only one value.
This is the argument for normalisation in a sentence: each fact should be stored in exactly one place. The join is the price, and the database is extremely good at joins.
The relational model is a small idea -- data in tables, relationships expressed as shared values rather than as pointers -- and its consequences are large. All of them are visible in four tables.
Store each fact once, identify it with a primary key, and reference it with a foreign key. The database then enforces the relationships for you. Flattening everything looks simpler until the first time you have to change something.
SQL is a declarative language: you describe the result you want, not the steps to produce it. SELECT name FROM customers WHERE country = 'UK' says nothing about indexes, scan order or join algorithms — the query planner decides all of that, and can change its mind as the data grows.
That separation is why a query written in 1995 still runs, and runs faster, on hardware and engines that did not exist then. It is also why two people write the same query differently and get the same plan.
The language is standardised (SQL:2023 is the current revision), and every engine adds its own extensions. The core — SELECT, joins, aggregation, transactions — is portable; date functions, string functions, upserts and window frame details are where dialects diverge.
| Engine | Character |
|---|---|
| PostgreSQL | Standards-compliant, extensible, strong JSON and geospatial support |
| MySQL / MariaDB | Ubiquitous in web hosting, fast simple reads, historically laxer about standards |
| SQLite | A single file, embedded in phones, browsers and applications everywhere |
| SQL Server | Microsoft's, deep integration with the .NET and Windows ecosystem |
| Oracle | Enterprise features and enterprise licensing |
SQLite deserves a mention beyond its size: it is probably the most widely deployed database in the world, and it is a full relational engine with transactions, constraints and window functions in a file you can email.
The honest comparison is not "relational versus NoSQL" but "which set of guarantees does this workload need".
Relational databases are the right default when your data has clear structure and relationships, when you need transactions across several records, when queries will be ad hoc and unpredictable, and when correctness matters more than raw write throughput. That covers the large majority of business applications.
Non-relational stores earn their place with genuinely schema-less documents, with write volumes that need horizontal sharding beyond what one node can serve, with key-value caching, with graph traversal many levels deep, and with time-series ingestion at enormous rates.
Note that the gap has narrowed from both sides. PostgreSQL stores and indexes JSON documents competently; several document databases now offer multi-document transactions. "Use the right tool" increasingly means "check whether the relational one already does it", because frequently it does.
Is a relational database slow? Not for the workloads it was designed for. A well-indexed PostgreSQL instance answers point queries in microseconds and handles tens of thousands of transactions a second on ordinary hardware.
What does "relation" mean? The mathematical term for a table — a set of tuples — not the relationships between tables, which is the common assumption.
Do I need to normalise everything? Normalise by default, denormalise deliberately for measured read paths. Starting denormalised because it might be faster is how schemas become unmaintainable.
Can relational databases scale? Vertically, very far. Horizontally, with more effort — read replicas are straightforward, sharding writes is not. Managed distributed SQL systems now offer horizontal scaling with relational semantics.
What is a view? A saved query that behaves like a table. It stores no data; it re-runs each time. A materialised view does store the result and needs refreshing.
Is JSON in a relational database a mistake? Not when used for genuinely variable data such as event payloads or user preferences. It becomes one when you put fields you regularly filter and join on inside a JSON blob instead of in columns.
About to run: press normalised. Before it does — what happens to the readout?
Committing to an answer first is the point — the reveal runs the experiment on the visualisation above and reads the real value back, so nothing here is scripted.
Say the answer out loud before you reveal it — recalling it is what makes it stick, and rereading it is not.
What does this module say about “The problem it solves”?
A relational database stores data in tables of rows and columns, where every table describes one kind of thing — customers, orders, products — and tables are connected by shared key values rather than by nesting data inside each other.
What does this module say about “Keys are the whole trick”?
The foreign key is what turns a pile of tables into a database. Declared properly, it makes an order pointing at a non-existent customer impossible — not unlikely, not caught by a validation function somewhere, but rejected by the engine for every client that ever connects.
What does this module say about “Why Split the Data at All”?
Switch this lab to one flat table and look at the red cells. The customer's name and city now repeat on every order they ever placed. That redundancy causes three classic problems:
Data split across tables and stitched back together by keys. Click a row to follow its relationships, then flatten everything into one table to see exactly what normalisation is protecting you from.