Module 1 · Foundations

The relational model

Beginner 14 min read No setup needed

SQL is strange until you understand that it is not a programming language in the usual sense — it is a way of describing a set of rows you want, resting on a piece of 1970s mathematics that turned out to be extraordinarily durable. Ten minutes here makes the next 27 lessons click.

After this lesson you can…

  • Explain what a table, row, column and key actually are — precisely.
  • Say why "declarative" changes how you should think about writing queries.
  • Describe the one idea (keys) that lets separate tables be reconnected.
  • Say when a relational database is the wrong choice.

1. The problem relations solve

🗃️

The spreadsheet that ate itself

You track orders in one big sheet. Every row repeats the customer's name, phone and address. Then Asha moves house — and her address is now wrong in 43 rows, right in 12, and you have no way to know which. Someone types "Kochi " with a trailing space and your city report splits in two. You cannot record a customer who has not ordered yet, because a row is an order.

The relational model's answer: store each fact exactly once, in a table about that kind of thing, and link tables by keys. Everything else in SQL follows from that.

ONE BIG TABLE — every fact repeated order | customer | phone | address | product | qty | price 1001 | Asha Nair | 98xxx11111 | 4 Marine Dr | Laptop | 1 | 65000 1002 | Asha Nair | 98xxx11111 | 4 Marine Dr | Mouse | 2 | 900 1003 | Asha Nair | 98xxx11111 | 9 Hill Rd | Monitor | 1 | 18000 ← which address is right? THREE TABLES — each fact stored once, linked by keys customers customer_id PK 7 Asha Nair 98xxx11111 9 Hill Rd changed ONCE ✓ orders order_id PK · customer_id FK 1001 → 7 1002 → 7 1003 → 7 order_items order_id FK · product_id FK 1001 · Laptop · 1 1002 · Mouse · 2 1003 · Monitor · 1 The trade you are making You gave up "everything in one place" and bought: one source of truth, no update anomalies, and the ability to record a customer with zero orders.
Splitting is not bureaucracy — it removes whole classes of bug. The cost is that you must rejoin the pieces when querying, which is what JOIN is for (lesson 09).

2. The vocabulary, precisely

Everyday wordFormal nameWhat it really means
TableRelationA set of rows with a fixed set of named, typed columns
RowTupleOne fact. "Customer 7 is Asha Nair in Kochi"
ColumnAttributeA named property with one data type for every row
Cell value—One value, or NULL meaning "not known / not applicable"
Primary keyPKThe column(s) that uniquely identify a row. Never NULL, never reused
Foreign keyFKA column pointing at another table's primary key
Schema—The full definition: tables, columns, types, constraints
"A table is a SET of rows" — and the consequences

A mathematical set has no order. That is why a query without ORDER BY may return rows in a different order tomorrow, even with the same data — the engine is free to return them however is fastest. If order matters, say so. This is not a bug you can work around; it is the model.

3. Declarative: say what, not how

results = []
for order in orders:                       # YOU choose to loop
    if order.status == "PAID":             # YOU choose the filter order
        for customer in customers:         # YOU choose the join algorithm
            if customer.id == order.customer_id:
                results.append((customer.city, order.amount))
totals = {}
for city, amount in results:               # YOU choose how to group
    totals[city] = totals.get(city, 0) + amount

You specified an algorithm. If customers grows to 10 million, this nested loop is now catastrophically slow — and nothing will fix it but rewriting your code. 

SELECT   c.city, SUM(o.amount) AS total
FROM     orders o
JOIN     customers c ON c.customer_id = o.customer_id
WHERE    o.status = 'PAID'
GROUP BY c.city;

You specified a result. The optimiser decides whether to use an index, which table to scan first, and whether to hash-join or merge-join — and it re-decides as your data grows, without you touching the query.

Why this matters for how you learn

Fighting the declarative model — writing loops with cursors, or pulling rows into your application to join them there — produces slow, fragile systems. The skill in SQL is expressing the question precisely and then, when it is slow, reading the plan to see what the optimiser chose (lesson 27).

4. Keys: the idea that makes it all work

CREATE TABLE customers (
    customer_id  INTEGER PRIMARY KEY,          -- unique, never NULL
    full_name    VARCHAR(100) NOT NULL,
    email        VARCHAR(150) UNIQUE           -- a "candidate key": also unique
);

CREATE TABLE orders (
    order_id     INTEGER PRIMARY KEY,
    customer_id  INTEGER REFERENCES customers(customer_id),   -- foreign key
    order_date   DATE NOT NULL
);

What the foreign key buys you

  • You cannot insert an order for customer 999 if that customer does not exist. The database refuses. Your application code does not have to remember to check.
  • You cannot delete a customer who still has orders (unless you say what should happen — cascade, set null, or restrict).
  • The optimiser learns from it and can sometimes eliminate a join entirely.
Surrogate vs natural keys

A natural key is real-world data that happens to be unique — email, ISBN, national ID. A surrogate key is a meaningless number the database generates. Surrogates win in practice, because real-world "unique" values turn out not to be: people change email, countries reissue IDs, and two suppliers use the same SKU. Use a surrogate PK, and add a UNIQUE constraint on the natural key (lesson 21).

5. The three relationship shapes

One-to-one users profiles Rare. Usually means the columns belong in one table — unless you are splitting off rarely-read or sensitive data. FK + UNIQUE on the child One-to-many ⭐ customers order order order The workhorse. 90% of the relationships you will model. FK lives on the MANY side Many-to-many orders order_items products An order has many products; a product is in many orders. Needs a JUNCTION table in the middle, holding both FKs — plus its own data (quantity, price).
There is no such thing as a direct many-to-many. It is always two one-to-many relationships with a junction table between them — and that table is usually where the interesting columns live.

6. When a relational database is the wrong tool

SituationBetter fit
Deeply nested documents with a shape that varies per recordDocument store (MongoDB) — or a JSON column (lesson 19)
Millions of writes/sec of simple key-value dataCassandra, DynamoDB, Redis
Relationships are the main thing you query (friends-of-friends, N hops)Graph database (Neo4j)
Analytical scans over petabytesColumnar warehouse / lakehouse (Snowflake, BigQuery, Spark)
Full-text relevance searchElasticsearch / OpenSearch
Almost everything elseA relational database. It is the default for good reasons
The honest caveat

Teams routinely reach for NoSQL to avoid schema design, then reimplement joins, constraints and transactions in application code — badly. Modern Postgres and MySQL handle JSON, full-text search and surprisingly large scale. Start relational; move a specific workload out when you have measured a specific reason.

A short history — useful for interviews
  • 1970 — Edgar Codd publishes "A Relational Model of Data for Large Shared Data Banks" at IBM, arguing data should be independent of how it is physically stored.
  • 1974 — IBM's System R implements SEQUEL, later renamed SQL.
  • 1979 — Oracle ships the first commercial SQL database, beating IBM to market.
  • 1986 — SQL becomes an ANSI standard. Every vendor then extends it differently, which is why dialects exist.
  • 1995–96 — MySQL and PostgreSQL arrive; open-source databases become viable.
  • 2000s — "NoSQL" promises to replace SQL for web scale.
  • 2010s–now — SQL comes back everywhere: NoSQL stores add SQL layers, and Spark, Kafka, Flink and every warehouse speak it. Window functions, CTEs and JSON are now standard.

The through-line: a declarative query language over a well-defined data model has outlasted every challenger, because it lets the storage engine change underneath without your queries changing.

Recap

  • Store each fact once, in a table about that kind of thing; link with keys.
  • A table is a set — unordered. No ORDER BY, no guaranteed order.
  • Declarative: you describe the result; the optimiser picks the algorithm.
  • Primary key identifies a row; foreign key points at one and is enforced by the database.
  • Prefer surrogate keys, with a UNIQUE constraint on the natural key.
  • Many-to-many is always two one-to-manys with a junction table.

Checkpoint

1 · A query without ORDER BY returned rows in id order yesterday and in a different order today, with unchanged data. What happened?
Row order is not part of the relational model. The engine might use an index scan one day and a parallel sequential scan the next — both correct, different orders. Relying on "it usually comes back sorted" is one of the most common sources of intermittent, unreproducible bugs. If order matters, write ORDER BY.
2 · You need to model "a student enrols in many courses; a course has many students, and we record the enrolment date". What do you build?
Many-to-many always needs a junction table, and notice that the relationship itself has data — the enrolment date belongs on the junction row, nowhere else. A comma-separated list makes querying, indexing and referential integrity impossible; a single FK on students could only model one course per student.
3 · Why is a surrogate primary key usually preferred over using the email address?
When a primary key changes, every foreign key referencing it must change too — and people do change email addresses. A surrogate key is stable by construction because it carries no meaning. You still put a UNIQUE constraint on email so the database enforces the business rule; you just do not build the relationships on top of it.