Open the orders table. It has no customer names. Each order has a customer_id:
orders
| id | customer_id | order_date | status |
|---|---|---|---|
| 101 | 1 | 2025-01-04 | delivered |
| 102 | 3 | 2025-01-09 | delivered |
| 103 | 1 | 2025-01-22 | delivered |
customers
| id | name | country |
|---|---|---|
| 1 | Ava Chen | CA |
| 2 | Liam Patel | CA |
| 3 | Sofia Rossi | IT |
customer_id is a foreign key (Chapter 5). Its value is the id of one row in customers. Order 102 has customer_id 3, so Sofia Rossi placed it.
This course writes a column as table.column when two tables are in view: orders.customer_id is the customer_id column of orders.
orders.customer_id = 3order 102
same value
customers.id = 3customer 3
read
Sofia Rossiname
Why split the data
One wide table could hold each order with the customer's name and email:
orders_wide (not in the shop)
| id | customer_name | customer_email | order_date |
|---|---|---|---|
| 101 | Ava Chen | ava.chen@example.com | 2025-01-04 |
| 103 | Ava Chen | ava.chen@example.com | 2025-01-22 |
| 113 | Ava Chen | ava.chen@example.com | 2025-05-12 |
- The same fact is stored many times.
- A change must update every copy. A missed copy makes the data disagree.
Split tables store each fact once. To see an order with its customer's name, you put the rows together again. That is a join, the subject of this chapter.
The links in Monster Market
| Column | Points to |
|---|---|
orders.customer_id | customers.id |
order_items.order_id | orders.id |
order_items.product_id | products.id |
products.category_id | categories.id |
employees.department_id | departments.id |
employees.manager_id | employees.id (the same table) |
Some rows have no partner:
- Customers 9 and 12 have no orders.
- Product 12 has no category. Category 5 (Posters) has no products.
- Employee 1 (the CEO) has no department. Department 5 (Legal) has no employees.
Joins treat these rows in different ways. The next lessons show how.