Tables, Rows & Schemas: The Relational Model

The Backend Engineering guide introduced databases at a glance: relational vs NoSQL, indexes, transactions. This guide slows down and goes deep. We start where every relational database starts — the table — because almost everything else (indexes, joins, transactions, isolation) is built on top of it.

The relational model in one sentence

Store data as tables: rows of records, columns of typed fields, and relationships between tables expressed by shared values. That's it. The model dates to 1970 and has outlived every "database killer" since, because rigidity — knowing exactly what shape your data has — turns out to be a feature, not a limitation.

A users table might look like this:

Each row is one user. Each column holds one field, and every value in a column shares a type — id is an integer, email is text, created_at is a timestamp. The database enforces those types: you cannot slip the word "hello" into created_at.

Columns have types, and types are guarantees

A type isn't just documentation — it's a promise the database keeps for you. Declaring total as DECIMAL(10,2) guarantees every value is a number with two decimal places, forever, across every app that writes to the table. Compare that to a plain file or a loosely-typed store, where one buggy client can write "twelve dollars" and poison your data silently.

Keys: how rows are identified and connected

Two kinds of key do all the relational work.

A primary key uniquely identifies each row. In users, that's id — no two users share one, and it's never null. It's the row's permanent name, the handle everything else uses to point at it.

A foreign key is a column in one table that holds the primary key of a row in another. That's the entire mechanism by which tables relate. An orders table connects to users by storing the user's id:

Rows 501 and 502 belong to Ada; 503 belongs to Alan. The relationship isn't stored as a nested object — it's just a shared value, user_id matching users.id. Joins are how you follow that link at query time.

The schema: rules enforced on every write

The schema is the full set of these declarations — which tables exist, their columns and types, and the constraints that further restrict valid data:

• NOT NULL — this column must have a value (an order must have a total).
• UNIQUE — no two rows may share this value (no two users with the same email).
• CHECK — a custom rule (total >= 0).
• FOREIGN KEY — this value must exist in the referenced table (you can't create an order for a user_id that doesn't exist).

CREATE TABLE orders (
  id       BIGINT PRIMARY KEY,
  user_id  BIGINT NOT NULL REFERENCES users(id),
  total    DECIMAL(10,2) NOT NULL CHECK (total >= 0),
  status   TEXT NOT NULL DEFAULT 'pending'
);

The crucial point: these rules live in the database, not the application. Every service, script, and admin query that touches orders inherits them automatically. An application bug that tries to write a negative total or an orphaned order is rejected at the door, not discovered three weeks later in a corrupted report.

Why this rigidity is worth it

Beginners often chafe at schemas — why declare everything up front? Because a schema is shared, enforced truth. It means you can reason about your data: every order has a valid user, every total is a non-negative number. That confidence is what the rest of this guide builds on. Indexes assume typed columns. Joins assume keys. Transactions assume the constraints they protect are real.

Recap

• The relational model stores data as tables: typed columns, record rows.
• Types are enforced guarantees, not hints — choose them deliberately (money is never a FLOAT).
• A primary key uniquely identifies a row; a foreign key points at another table's primary key — that's how tables relate.
• The schema bundles the structure plus constraints (NOT NULL, UNIQUE, CHECK, FOREIGN KEY), enforced on every write.
• Rules living in the database mean every client inherits the same integrity — bad data is stopped at the door.

We have well-shaped tables. But finding the right rows among millions, quickly, needs one more structure. Continue to How Indexes Work: Inside the B-Tree.