We have used users, products, and orders, usually one table at a time. Real applications rarely force everything into one giant table. Different information has different structures and purposes; mixing it creates duplication and makes changes harder. A central relational-database idea is to store each kind of data separately and connect it through related fields.
One “big table” looks convenient
Each row below tells us who bought what, but Alice's name and email repeat with every order. If her email changes, every historical order must be updated. Missing even one row creates inconsistent data.
| order_id | user_name | user_email | product_name | quantity |
|---|---|---|---|---|
| 1 | Alice | alice@example.com | Mouse | 1 |
| 2 | Alice | alice@example.com | Coffee Mug | 2 |
| 3 | Bob | bob@example.com | Keyboard | 1 |
Separate storage, combined when needed
A more common design stores each user once in users, each product once in products, and references only user_id, product_id, and quantity from an order. Instead of copying Alice's details, an order stores user_id = 1 and looks up that user when needed.
| id (PK) | name | |
|---|---|---|
| 1 | Alice | [email protected] |
| id (PK) | user_id (FK) | product_id (FK) | quantity |
|---|---|---|---|
| 1 | 1 | 2 | 1 |
| id (PK) | name | price |
|---|---|---|
| 2 | Mouse | 40 |
orders.user_id → users.idone user can have many ordersorders.product_id → products.idone product can appear in many ordersPrimary keys and foreign keys
A primary key uniquely identifies one row. users.id works well because two people named Alice can still have different IDs. Uniqueness is essential: two users cannot share the same primary-key value.
A foreign key points to another table's primary key. orders.user_id points to users.id and tells us who owns the order; orders.product_id points to products.id and tells us which product was purchased.
The two links above are orders.user_id → users.id and orders.product_id → products.id. The left side is a foreign key stored by the order; the right side is the unique primary key it references.
Why is this easier to maintain?
If Alice changes her email, only one user row changes. Her orders still hold user_id 1, so their relationship remains valid. Renaming a product changes one product row rather than every historical order. This reduces duplication and inconsistency.
UPDATE users
SET email = '[email protected]'
WHERE id = 1;One-to-many relationships
One user can have many orders, while each order normally belongs to one user. This is a one-to-many relationship. In this simplified model, one product can also appear in many orders. You do not need advanced design theory yet. Keep the central idea: IDs establish relationships, and the next section's JOIN recombines the data.
For example, orders 1, 2, and 5 can all reference Alice through user_id = 1. That is “one user to many orders,” without copying Alice's details into three rows.
Connect the fields that belong together
Click one field, then its related field in another table. Complete two relationships. Relationships use IDs—not values that merely look similar.
No relationships yet (0 / 2)
Select a foreign key in orders, then the primary key it actually references.Repair duplicated order data
A café stores order_id, customer_name, customer_email, drink_name, drink_price, and quantity in orders. Design customers, drinks, and orders: list their columns, mark primary and foreign keys, and explain how many rows change when a customer updates an email.
Choose reliable keys
An online shop allows duplicate user names and product renaming. Decide which relationships are reliable and explain why: (1) orders.user_name → users.name; (2) orders.user_id → users.id; (3) orders.product_name → products.name; (4) orders.product_id → products.id.