Learn / SQL Fundamentals / Tables & Relations
Lesson 1 of 6

Tables & Relations

Imagine trying to run a library out of one giant spreadsheet: every row has a book title, an author name, an author birth year, a borrower name, a borrower phone number, and a due date — all crammed into one sheet. The moment an author's birth year is wrong, you have to fix it on every single row that mentions that author. Relational databases exist to fix exactly this problem: instead of one giant sheet, you split data into focused tables that reference each other.

💡 The Bottom Line

A relational database stores data in tables — rows and columns, like a spreadsheet — but splits related information across multiple tables instead of one giant one. Each table gets a primary key to uniquely identify its rows, and foreign keys let one table point to a row in another. That's the whole trick: small, focused tables, linked together, so every fact lives in exactly one place.

Rows, columns, and one focused idea per table

A table holds one kind of thing. A books table holds books; an authors table holds authors. Each row is one record, each column is one attribute of that record:

authors
+----+----------------+------------+
| id | name           | birth_year |
+----+----------------+------------+
| 1  | Ursula K. Le Guin | 1929    |
| 2  | Octavia Butler    | 1947    |
+----+----------------+------------+

books
+----+---------------------+-----------+
| id | title               | author_id |
+----+---------------------+-----------+
| 1  | The Left Hand of Darkness | 1   |
| 2  | Kindred             | 2         |
| 3  | The Dispossessed    | 1         |
+----+---------------------+-----------+

Notice books doesn't repeat the author's name or birth year — it just stores author_id, a pointer to the matching row in authors. If Le Guin's birth year were ever wrong, there'd be exactly one row to fix, not three.

Primary keys: one row, one identity

The id column in each table is its primary key — a value that's unique for every row and never changes. It's how the database (and other tables) refer to "this exact record" unambiguously, even if two authors happen to share a name.

⚠️ Watch Out

Don't use something like a person's name or email as a primary key just because it "looks" unique. Names collide, emails change. That's why most tables use a plain auto-incrementing number (or a generated ID) as the primary key — it has no meaning other than "row identity," so it never needs to change.

Foreign keys: how tables point at each other

A foreign key is a column in one table that stores the primary key value of a row in another table. books.author_id is a foreign key pointing at authors.id. This is the actual "relational" part of "relational database" — it's how two separate, focused tables stay connected without duplicating data.

CREATE TABLE authors (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  birth_year INTEGER
);

CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  author_id INTEGER REFERENCES authors(id)
);

🙋 There Are No Dumb Questions

Q: Why not just put the author's name directly in the books table and skip the separate authors table entirely?
A: You could — for a tiny toy project it might not matter. But the moment you need to store an author's birth year, nationality, or a bio, you'd either repeat that data on every one of their books (and risk it going out of sync) or leave it blank on most rows. Splitting authors into their own table means each fact about an author lives in exactly one row, no matter how many books they've written.

Why split the data at all?

This idea — storing each fact once, in the table it belongs to, and linking tables together with keys — is called normalization. The payoff is real: update an author's birth year once, and every book by that author automatically reflects the correct value the next time you look it up, because there's no duplicate copy to forget about.

🔧 Try It Yourself

Sketch two tables for a simple blog: authors and posts. Decide what columns each needs, which column is the primary key in each, and which column in posts is the foreign key pointing back to authors. Write it out as two small tables like the ones above.

✅ Quick Check

In the books table above, what is author_id?

Next up: now that you know how data is organized into tables, it's time to actually ask questions of it — that's what SELECT and WHERE are for.

← Back to
Next →