Tables, Rows & the Relational Model

Before you write another query, it's worth thirty seconds on the idea underneath all of them — the relational model. It's the reason data in SQL looks the way it does, the reason tables connect the way they do, and the quiet explanation behind a dozen behaviors that otherwise feel like arbitrary rules. Learn the model once and the rest of the course is details.

Tables, rows, columns

A relational database organizes everything into tables. One table, one kind of thing:

menu_items
 id |      name       | category | price | in_stock
----+-----------------+----------+-------+----------
  1 | Espresso        | coffee   |  2.50 | t
  2 | Cappuccino      | coffee   |  3.80 | t
  5 | Earl Grey       | tea      |  3.00 | t
  7 | Croissant       | pastry   |  3.20 | t

• Each row (also record) is one instance of that thing — one menu item, one customer, one order.
• Each column (also attribute or field) is one fact about it, with a name and a data type: name holds text, price holds numbers, in_stock holds true/false. A row can't smuggle a paragraph into price — the type is a promise the database enforces.
• The table's design — its column names and types — is called its schema. The rows are the data; the schema is the shape the data must fit.

The discipline this buys you is enormous: any question about menu items is a question about this one table, and every row in it is guaranteed to have the same shape.

Codd's big idea

The model comes from a 1970 paper by IBM's Edgar Codd, and its radical move is easy to miss because it won so completely: data should be described by what it is, not by where it lives.

Before Codd, databases were navigational — records physically pointed at other records, and a query was literally a traversal: start at this customer, follow the pointer chain to their orders. Programs were welded to the storage layout; reorganize the disk and you rewrote the code. Codd's proposal: store everything as plain tables of values, connect them by values (an order stores the customer's id, not a pointer), and let queries say what they want in logic, not navigation. The database becomes free to store, index, and reorganize however it likes — which is exactly the declarative bargain from lesson 1.1, one layer down.

Two consequences of the model that will follow you through the whole course:

• Every value lives in a table. There are no free-floating variables, no "the current customer" — if a fact matters, it's a value in a row in a table.
• Rows have no built-in order. A table is a set of rows, not a list. The database may return them in any order it finds convenient — insertion order today, something else after the data grows. When order matters, you ask for it (ORDER BY, lesson 2.3 — and the "it was sorted yesterday" bug that bites everyone who assumes).

Keys: how a row gets an identity

If rows are connected by values, some value has to say which row. That's a key.

A primary key is a column (sometimes a combination) whose value uniquely identifies each row — menu_items.id above. Names make terrible keys: two customers named Ada, a typo'd "Espresso ", a product renamed for marketing. So tables almost always carry a dedicated id column that means nothing except this exact row, forever.

Now connections are just values. A café's orders don't contain the customer — each order stores the customer's id in a foreign key column:

customers                        orders
 id |  name  |  city             id  | customer_id |     product      | amount
----+--------+--------          -----+-------------+------------------+--------
  1 | Ada    | Berlin            101 |           1 | Espresso Machine | 220.00
  2 | Grace  | Lisbon            102 |           1 | Grinder          |  92.00
  3 | Linus  | Berlin            103 |           2 | Kettle           |  45.00

Read orders.customer_id and you can see the relationships: orders 101 and 102 both point at customer 1 — Ada. This is a one-to-many relationship (one customer, many orders), the workhorse shape of every database you'll meet. Its siblings: one-to-one (one person, one passport — rarer, usually two tables that could be one) and many-to-many (students and courses — each student takes many courses, each course has many students), which needs a third table in the middle. Section 5 builds all three; for now it's enough to see that relationships are values, not wires.

The engines vs. the language

One distinction to lock in before the tour of tools next lesson. The relational model is the idea. SQL is the language for talking to it. And an RDBMS — a relational database management system — is a concrete program implementing both:

• PostgreSQL — the feature-rich open-source flagship this course teaches on.
• MySQL / MariaDB — the web's long-time workhorse.
• SQLite — a tiny embedded engine; it ships inside your phone, your browser, most apps you own.
• SQL Server (Microsoft) and Oracle — the enterprise pair.
• DuckDB — the analyst's new in-process favorite.

They all speak SQL; each has an accent. The course teaches standard-leaning PostgreSQL and flags the accents as they matter — so your skills travel with you between jobs and engines.

Next lesson: a tour of those tools, and the lab you'll actually use — the live PostgreSQL sitting in every playground on this site.