The relational model
The most common kind of database stores everything in TABLES. A table is just a grid: columns are the kinds of fact you're storing (name, city, price), and each row is one thing (one user, one order). It looks exactly like a spreadsheet — and that's a good first picture. The one new idea is how tables connect to each other: instead of copying a user's whole details into every order they make, you give each user an id, and the order just remembers that id. That single trick is what makes databases tidy.
A table is rows and columns
Picture a Users table. The columns name the facts: id, name, city. Each row is one user: '1, Maya, Pune'. Another row: '2, Ali, Delhi'. That's it — a table is a labelled grid where every row has the same set of columns. The word 'relational' sounds fancy but just means the data is organized into these tables (the old math term for a table is a 'relation'). Every relational database — the SQL ones — is built on this single shape. Learn to see your data as tables and you've understood 80% of databases.
The primary key: a unique id for each row
Notice the 'id' column. Every table has one column whose job is to give each row a unique label, so you can point at exactly one row with no confusion. That's the PRIMARY KEY. Why not just use the name? Because two different people can both be named 'Maya' — names aren't unique. An id is. So '1' always means that one specific Maya, forever, even if she changes her name or city. The primary key is the row's permanent, unmistakable handle. Almost always it's a plain number that counts up: 1, 2, 3…
The foreign key: how tables link by id
Now suppose Maya places orders. The wrong way: copy 'Maya, Pune' into every single order row. Do that and you've written her details a hundred times — and if she moves cities, you'd have to fix a hundred rows, and you'll surely miss some. The relational way: the Orders table has a column user_id that just stores Maya's id — the number 1. That id, living in Orders but pointing back to a row in Users, is called a FOREIGN KEY. It means 'this order belongs to the user whose primary key is 1.' Her name and city are written down ONCE, in Users. Every order simply references her by id. Move her to Mumbai? Change one row in Users; every order automatically reflects it, because they never stored a copy in the first place.
Why this beats one giant table
You might wonder: why not just keep ONE big table with everything — order id, item, user name, user city — all in one row? Because then every fact about Maya is duplicated across all her orders. Duplication is the enemy: it wastes space, and worse, it lets the data DISAGREE with itself. Update her city in one order row and forget another, and now your database says she lives in two cities at once. Which is true? Splitting data into linked tables — Users here, Orders there, joined by id — means each fact lives in exactly one place, so it can never contradict itself. Tidying data this way has a name, NORMALIZATION, and it's a whole lesson of its own. The relational model is what makes it possible.
It's a tradeoff
| Option | 👍 Pro | 👎 Con |
|---|---|---|
| Separate tables linked by id (the relational way) | Each fact stored once, so it can't contradict itself. Update a user's city in one place and every order reflects it. Tidy and trustworthy. | To see an order WITH the user's name, the database must combine two tables (a 'join') — one extra step, which the database handles for you. |
| One giant table with everything copied in | Everything for a row is right there — no combining tables to read it. | The user's details are duplicated across every order. Update one copy and forget another, and the data disagrees with itself — the classic mess relational databases exist to prevent. |
Questions you might have
▸If it's basically a spreadsheet, why not just use a spreadsheet?
A spreadsheet is great until the data grows or links up. A database holds billions of rows, finds any one instantly, enforces rules like 'every order must point at a real user', and lets thousands of people use it at once. The relational model is the spreadsheet idea PLUS those guarantees.
▸What exactly makes a primary key different from any other column?
Two promises: it's unique (no two rows share it) and it's never empty. That's what lets it stand in for 'this exact row, no other.' Other columns can repeat (two users in Pune) or be blank; the primary key never can.
▸Is a foreign key a copy of the other row?
No — it's just a pointer. It stores ONLY the other row's id (a number like 1), not a copy of its data. That's the whole point: the details live in one table, and other tables reference them by id, so nothing is duplicated.
▸What stops me from putting a user_id in Orders that doesn't match any real user?
The database can! When you declare a column a foreign key, the database refuses to store an order pointing at a user id that doesn't exist, and refuses to delete a user who still has orders. That rule keeps the links honest — it's called referential integrity.
▸Do I have to combine tables every time I want to read data?
Only when you want columns from more than one table at once (an order AND the buyer's name). Reading just one table is direct. Combining two tables by their matching ids is called a 'join', and it's so common the database makes it easy — there's a whole lesson on it.
Best read after: What is a database?