The idea
A relational database is a set of tables. Each table holds one kind of thing: customers, orders, products. Each row is one of those things, and each column is one fact about it: a customer's city, an order's date, a product's price.
That sounds like a spreadsheet, and it nearly is. The difference is the rule behind it: every fact is stored once, in the table it belongs to, and tables point at each other with keys. A query then gathers the facts it needs from wherever they live.
Why it exists
Imagine Harbor & Pine running on one big sheet, a row per order line with the customer's name and city typed in, plus the product and its category. It works for a week.
Figure 1
From one big sheet to separate tables
| order_id | first_name | city | product_id | category |
|---|---|---|---|---|
| 1231 | Gary | Chicago | 165 | Textiles |
| 1231 | Gary | Chicago | 129 | Lighting |
| 1734 | Charles | Las Vegas | 166 | Textiles |
| 4230 | Gary | Chicago | 135 | Lighting |
| 4230 | Gary | Chicago | 191 | Decor |
| 4230 | Gary | Chicago | 177 | Textiles |
| customer_id | first_name | city |
|---|---|---|
| 69 | Gary | Chicago |
| 101 | Charles | Las Vegas |
| order_id | customer_id |
|---|---|
| 1231 | 69 |
| 1734 | 101 |
| 4230 | 69 |
| order_id | product_id |
|---|---|
| 1231 | 165 |
| 1231 | 129 |
| 1734 | 166 |
| 4230 | 135 |
| 4230 | 191 |
| 4230 | 177 |
- Six order lines kept the spreadsheet way: one row per line, with everything about the order and the customer typed into it.
- Gary's name and city are stored five times. Order 4230 is stored three times. The sheet repeats a fact every time it is needed.
- Gary moves to Denver. Five cells must change. Miss one and the sheet says he lives in two cities at once, and every report that groups by city is quietly wrong.
- Split it. Each customer becomes one row in
customers, with acustomer_idthat never changes. - Each order becomes one row in
orders, holding only thecustomer_idof who placed it. - The lines go to
order_items, one row per product in an order. Now every fact lives in exactly one place, and the move to Denver is a single update.
The trouble with the sheet is not its size. It is that the same fact sits in many cells, and the copies drift apart. Someone fixes a typo in one row and not in the others. A customer changes their email and half their history keeps the old one. Deleting the last order of a customer deletes the only record that the customer exists. Those three failures have names (update, insert and delete anomalies), and every one of them comes from storing a fact twice.
Splitting the data into tables fixes all three at once, because there is only one copy to keep
right. The price is that the answer to a question is now spread over several tables. That is what
JOIN is for, and why it gets a whole chapter.
How it works under the hood
Tables, rows and columns
A table has a fixed list of columns, each with a type: whole numbers for quantity, decimals for
unit_price, dates for order_date, text for city. Every row has a value (or NULL, "no
value") in every column. The rows have no built-in order. "The first row" means nothing until a
query says ORDER BY.
The grain of a table is what one row stands for. In orders it is one order; in order_items
one line of an order; in web_sessions one visit to the site. Knowing the grain tells you what
COUNT(*) counts and which numbers are safe to add up.
Keys
A primary key is the column that tells rows apart. No two rows share it and it never changes:
customer_id in customers, order_id in orders. Names make bad keys (three different customers
are called Michael Chavez); ids are made to be keys.
A foreign key is a column that holds another table's primary key. orders.customer_id says
which customer placed the order. It is the thread a JOIN follows.
Figure 2
Primary keys and foreign keys
| order_item_id | order_id | product_id | unit_price |
|---|---|---|---|
| 601 | 1231 | 165 | 25.99 |
| 602 | 1231 | 129 | 109.00 |
| 1828 | 1734 | 166 | 26.99 |
| 8230 | 4230 | 135 | 149.00 |
| product_id | category | unit_price |
|---|---|---|
| 129 | Lighting | 109.00 |
| 135 | Lighting | 159.00 |
| 165 | Textiles | 25.99 |
| 166 | Textiles | 28.99 |
- Every table has a primary key: a column whose value is different on every row.
order_item_idnames exactly one line,product_idexactly one product. order_items.product_idis a foreign key: it holds a product's primary key. Following it from a line always lands on one product.- Following it the other way, one product can have many lines. That is a one-to-many relationship, the most common shape in any database.
- Not every repeated column is a mistake.
unit_priceon the line is what the customer paid on the day;products.unit_priceis today's list price. Line 8230 paid 149.00 for a lamp that now costs 159.00.
The database can enforce both. A primary key constraint rejects a duplicate id; a foreign key
constraint rejects an order for a customer who does not exist. Analysts rarely create these, but
knowing they exist tells you what you can trust. If order_id is the primary key of orders, a
join to orders on order_id can never return more than one order per line.
NULL: the value that isn't there
NULL is not zero and not an empty string. It means "no value": a customer who didn't say how they
heard about the store has referral_source NULL, a product whose cost is unknown has unit_cost
NULL. Because the value is unknown, any comparison with it is unknown too. NULL = NULL is not
true, and WHERE referral_source = 'search' skips the NULL rows without a word. You ask for them
with IS NULL. Aggregates skip them as well: AVG(unit_cost) averages only the products that have a
cost, and COUNT(unit_cost) counts only those, while COUNT(*) counts every row. When a number
looks too small or too large, the NULLs are the first place to look.
Normalisation, in plain words
Normalisation is the name for designing tables so each fact is stored once. The textbook defines "normal forms"; in practice they come down to three questions about every column:
- One value per cell. A column holds one thing, not a list. Three products in one cell
("165, 129, 177") is a sign the data needs its own table, here
order_items. - About the whole key. A column describes the row's key and nothing else. A product's category
depends on the product, not on the order line, so it lives in
products. - Nothing but the key. If a column can be worked out from another non-key column (a city from the customer), it belongs in that other table.
Follow the three and you get the store's layout.
Figure 3
The Harbor & Pine store, table by table
customers
- customer_id
- first_name
- city
- state
orders
- order_id
- customer_id
- order_date
- status
order_items
- order_item_id
- order_id
- product_id
- quantity
products
- product_id
- name
- category
returns
- return_id
- order_item_id
- reason
- Five of the store's tables. Each line is a key: the 1 end holds the primary key, the * end holds it as a foreign key.
- One customer, many orders:
orders.customer_idpoints atcustomers. - One order, many lines; one product, many lines.
order_itemssits in the middle and connects orders to products. - A return belongs to one order line. To get from a return to a customer you walk the keys:
returns→order_items→orders→customers. That walk is a chain of JOINs.
Real databases bend the rules on purpose. order_items.unit_price repeats a price because the
price on the day of the sale is a different fact from today's list price. Reporting tools go further
and rebuild wide tables (a "star schema", which the Power BI track covers) because reading is easier
when fewer joins are needed. Those are choices made with the rules in mind, not instead of them.
Common misconceptions
"A database is a big spreadsheet"
A spreadsheet is a grid of cells you can put anything into. A table is a set of rows that all have the same columns, with types and keys. That is what lets a database answer questions over millions of rows and keep them consistent while many people write to it.
"Splitting the data loses information"
Nothing is lost: the keys keep every connection. Any version of the big sheet can be rebuilt with joins whenever a question needs it, and it will always be up to date, because it is built from the single copy of each fact.
"Rows are stored in the order you see them"
Tables have no order. Two runs of the same query without ORDER BY may return rows in a different
order, and both are correct. When order matters for an answer, the query has to ask for it.
Check yourself
One question. Answering it ticks this lesson off on the map; it never locks a level.
Not quite. That is what the one-big-sheet version would need. order_items holds a product_id, not the category, so the lines don't change.Try another answer.
Right. The category is a fact about a product, so it lives in products. Every line and order reaches it through product_id, and sees the new name at once.
Not quite. orders doesn't store products at all: an order's lines are in order_items, and those only hold product_id.Try another answer.
Not quite. The category is stored data in products[category]. It just isn't copied anywhere else, so the change happens in one table.Try another answer.
Lesson done: it is ticked on the map.