A Relational Model of Data for Large Shared Data Banks
1970 · Communications of the ACM · ~12 min read
Read the original paperIn 1970 a program had to know how data was physically stored to read it. Codd proposed describing data as plain tables and asking questions in logic, and every SQL database is the result.
The problem: programs that knew too much
In 1970 a database was a set of records chained together by pointers on disk, and a program read it by navigating: start at a department record, follow the pointer to its first employee, follow the next-pointer to the second, stop when the name matches. The two dominant designs, IBM’s hierarchical IMS and the CODASYL network model, differed in the shapes of the chains but agreed on the premise that the application should know how the data was laid out.
The cost showed up the moment anything changed. Add an index, split a file, reorder a chain for performance, and every program that navigated it broke, because the navigation was the program. Different programs stored the same fact in several places and let them drift apart. Asking a new question meant writing a new navigation. Edgar Codd, a mathematician at IBM, thought the whole arrangement had the layers backwards.
The idea: describe the data, not the path to it
Codd proposed that users see data as nothing but relations: tables of rows, each row a tuple of values drawn from named domains, with no ordering, no duplicates, and no pointers. Records relate to each other by containing the same value, the way an employee row and a department row both contain “ENG”. The storage engine may keep the tables however it likes, with whatever indexes and chains it finds efficient, and may change its mind tomorrow; the user’s view does not move. He called this data independence, and it is the paper’s real subject.
How it works
Having defined the view, Codd defined the operations on it, and this is why the paper is mathematics rather than opinion. A handful of operators take relations and return relations: select rows that satisfy a condition, project onto some of the columns, join two tables on equal values, and a few more. Because each result is again a table, operations compose, and a question of any complexity is an expression. SQL, which arrived a few years later, is a friendlier spelling of this relational algebra.
Filter rows, choose columns and decide whether to join. Note that you never say how to find anything: no pointers, no file offsets, no loop. The result is itself a table.
| name | dept | salary |
|---|---|---|
| Asha | ENG | 92 |
| Ben | ENG | 71 |
| Chen | OPS | 58 |
| Dara | SALES | 64 |
| Eli | ENG | 55 |
| Fola | SALES | 83 |
The join matches rows by a shared value (dept) rather than by a stored link. Codd’s point: because tables relate by value, the storage engine may lay them out however it likes, and your query still means the same thing.
The paper also starts the discipline of normalisation. Codd insists that every value be atomic, no lists inside a cell, so that the operators have something uniform to work on, and he shows how to decompose a relation that carries repeating groups into several that do not. Redundancy and the inconsistencies it breeds, the practical disease of the pointer databases, become a property you can reason about and remove.
One more consequence falls out and turned into an industry: if a query says what rather than how, something has to decide how. That something is the query optimiser, and the decades of work on it, from System R’s cost-based planner onward, exist because the relational model left a gap exactly its shape.
What it changed
IBM was slow to act on its own researcher’s idea, since it sold IMS, but the System R project built the first serious prototype and invented SQL in the process; Berkeley’s Ingres became the lineage that leads to Postgres; a small company called Relational Software shipped the first commercial product in 1979 and renamed itself Oracle. Codd received the Turing Award in 1981. For forty years the relational model has absorbed every challenger: object databases in the 1990s, XML stores in the 2000s, and the NoSQL systems of the 2010s, most of which have since added a SQL dialect of their own.
Where you meet it today
- Every SQL database, and the query planners in them: an
EXPLAINoutput is the optimiser choosing a path on your behalf. - NoSQL growing tables. Cassandra’s CQL, DynamoDB’s PartiQL and MongoDB’s aggregation pipelines are the relational operators returning under other names.
- Data lakes. Spark SQL, Trino and BigQuery run relational algebra over files; the model outlived the storage it was invented for.
- Schema design. Normal forms, foreign keys and the question “is this value stored twice?” are Codd’s questions.
The original. Read it after this page; the explainer above is a map, not a substitute.
Eleven years on, Codd on what the model had become and what it was always for.
Free lectures, notes and projects; the relational model is lecture one.