The relational model - tables, rows and columns
Why almost every serious database organizes data into tables, and the set theory quietly sitting underneath them.
The class attendance register
Picture a school attendance register. It's a grid: one row per student, one column per piece of information about that student — name, roll number, present-or-absent. Every row has the same columns, in the same order, so you can always answer "what's in the 'name' column for row 5?" instantly, without reading anything else.
That grid — rows and columns, all rows sharing the same columns — is the entire idea behind the relational model, invented by Edgar F. Codd at IBM in 1970. It's called "relational" not because tables relate to each other (though they do), but because "relation" is the mathematical term for what we're calling a table. Every relational database — PostgreSQL, MySQL, SQLite, Oracle, SQL Server — is fundamentally just a very fast, very safe way to manage a big pile of these grids.
The vocabulary, precisely
COLUMNS (attributes)
┌────────┬───────────┬───────┐
│ id │ name │ marks │
├────────┼───────────┼───────┤
ROW ───▶ │ 101 │ Shagun │ 92 │
(a record) ├────────┼───────────┼───────┤
│ 102 │ Rohan │ 78 │
├────────┼───────────┼───────┤
│ 103 │ Radhika │ 85 │
└────────┴───────────┴───────┘
TABLE: students
- Table (relation) — the whole grid. Represents one kind of thing: students, orders, products.
- Row (tuple / record) — one specific instance of that thing. One student.
- Column (attribute / field) — one property every row has. Name, marks, id.
- Schema — the definition of a table's columns and their types, agreed upon before any data goes in. You decide up front: "the
studentstable has an integerid, a textname, and an integermarks" — and the database then refuses any row that doesn't match that shape. This is why relational databases are often called schema-on-write: the shape is fixed before you write data (contrast this with some NoSQL databases in Module 8, which are schema-on-read).
Why a grid, mathematically
Here's the part almost nobody explains, and it's the part that makes everything else in this course click: a table isn't just "a grid we found convenient." It's a direct, literal implementation of a mathematical set of tuples.
In set theory, a tuple is just an ordered list of values, like . A relation is defined as a set of tuples that all have the same structure (the same number of elements, each drawn from the same domain — id from the set of integers, name from the set of strings, and so on).
Why does this matter practically? Because sets have well-defined, precise operations — union, intersection, difference, filtering (called selection), and picking specific columns (called projection) — all worked out by mathematicians decades before SQL existed. When you write a
SELECT query with a WHERE clause, you are literally invoking the set-theory operation of selection:
The (sigma) symbol here means "filter the set, keeping only tuples matching this condition." SQL's
WHERE marks > 80 is just readable syntax for exactly this operation. Similarly, SELECT name FROM students is the projection operation, written — "keep only this column from every tuple." When we cover JOIN in Module 3, you'll see it's the set-theory Cartesian product combined with a selection. None of SQL is arbitrary — it's a readable skin over relational algebra, a small, precise mathematical system.
This matters in practice because it's why database engines can be so aggressively optimized: because the operations have precise mathematical definitions, the database is free to execute your query in whatever order or strategy is fastest, as long as the mathematical result is identical. You ask what you want (declaratively, as mentioned in the last lesson); the query planner decides how, using these algebraic guarantees as its rulebook.
Primary keys: making every row findable
A set, mathematically, cannot contain duplicate elements — but nothing so far stops two rows from looking identical (two students both named "Rohan" with the same marks, say). To make every row uniquely and reliably identifiable, relational tables use a primary key: one column (or combination of columns) declared to be unique for every row, forever.
CREATE TABLE students (
id INTEGER PRIMARY KEY,
name TEXT,
marks INTEGER
);
id is the primary key. The database now guarantees, by rejecting any violating insert, that no two rows will ever share an id. This single guarantee is what lets every other part of the relational model work: it's how rows in different tables refer to each other (a foreign key, covered properly once we reach joins), and it's the anchor an index (Module 4) is built around.
A first taste: creating and looking at a table
-- Define the shape
CREATE TABLE students (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
marks INTEGER
);
-- Put a row in
INSERT INTO students (id, name, marks) VALUES (101, 'Shagun', 92);
-- Look at everything
SELECT * FROM students;
WHERE-less SELECT * above to SELECT name FROM students WHERE marks > 80 once you reach the next module — that's projection and selection, together, in one line.
Common mistakes
- Thinking column order matters. Mathematically and practically, a row is identified by column names, not position.
SELECT name, id FROM studentsis completely valid even though it's "backwards" from theCREATE TABLEorder. - Forgetting a primary key "because the app doesn't need one yet." Without one, duplicate rows can silently pile up, and — critically — other tables have nothing reliable to link back to. Always give a table a primary key.
- Assuming a table always represents "an object" in the OOP sense. Sometimes a table represents a relationship itself (e.g. an
enrollmentstable linking students to courses) rather than a "thing." This becomes important in the ER modeling lesson in Module 4.
Summary
The relational model represents every kind of data as a table: a mathematical set of same-shaped tuples (rows), each row uniquely identifiable via a primary key. This isn't an arbitrary design choice — it's a direct implementation of relational algebra, which is exactly why SQL'sSELECT/WHERE/JOIN map so cleanly onto set operations like projection, selection, and product. Next, we'll look at the columns themselves in detail — the specific data types a database enforces, and the surprisingly deep idea of what it means for a value to be missing (NULL).