Why Data Is Split into Tables

So far, every query has read a single table. But the answer to an everyday question about the shop is often spread over several. Who placed order 4? The table orders knows only the customer's ID. And what was in it? That's in order_items, and again only as the IDs of the games. Why does the database keep things apart like this, when it makes such questions harder?

Imagine that the shop kept everything in one big table, with a row for each item of each order, and everything about the item right there in its row. Emma Clark's orders would look like this:

It's easy to read, but the same things are in it again and again. Emma's name, email and city are in every row of every order she has placed, and the title of each game would be in the row of every order it was in. That causes trouble:

• Every change must be made in many places. When Emma changes her email, it has to change in all her rows. Miss one, and the table says two different things, and nobody knows which one is true.
• Typos split things up. If one row says “Emma Clarke”, she suddenly looks like two customers.
• Some things have nowhere to go. A customer who hasn't ordered anything yet, or a game that nobody has bought, would have no row.
• The table is needlessly large, because the same text is stored many times over.

That's why a database splits its data into tables, so that each thing is stored exactly once: each customer has one row in customers, each game one row in games. As you saw in the first lesson, each of these rows has a primary key, the column id, with a number that no other row of the table has. Other tables point at the row with this number, in a foreign key: customer_id in orders holds the id of a customer, game_id in order_items the id of a game. When Emma changes her email, it changes in one row, and all her orders still point at her.

The catch is that the answer to one question is now spread over several tables. With what you know so far, you have to follow the keys yourself, one query at a time. First, who placed order 4:

SELECT customer_id
FROM orders
WHERE id = 4;

The customer with the ID 1. You read the number in the result and use it in a second query:

SELECT name, email
FROM customers
WHERE id = 1;

It works, but it's slow work: you had to copy a number from one result into the next query. A list of all thirty orders with the names of their customers would take thirty-one queries. In the next exercise, you'll let the database do it: you'll join the tables in one query.