Databases · interactive
Tables, keys and relations
Five orders from three customers. Store them in one sheet and Ada's email is copied three times. Split them into two tables linked by a key and every fact lives in one place. Try each statement and see what the database allows.
customers
| id (PK) | name | |
|---|---|---|
| 1 | Ada | ada@new.io |
| 2 | Bo | bo@mail.com |
| 3 | Cy | cy@mail.com |
orders
| id | customer_id (FK) | total |
|---|---|---|
| 101 | 1 | 40.00 |
| 102 | 2 | 15.50 |
| 103 | 1 | 22.00 |
| 104 | 3 | 9.99 |
| 105 | 1 | 61.25 |
The same data in one flat sheet
| order | name | total | |
|---|---|---|---|
| 101 | Ada | ada@mail.com | 40.00 |
| 102 | Bo | bo@mail.com | 15.50 |
| 103 | Ada | ada@mail.com | 22.00 |
| 104 | Cy | cy@mail.com | 9.99 |
| 105 | Ada | ada@mail.com | 61.25 |
The flat sheet repeats Ada's email on 3 rows. Miss one and the data disagrees with itself.
UPDATE 1. One cell changed, and every order of Ada's sees the new email through the key.
Run a statement
UPDATE customers SET email = 'ada@new.io' WHERE id = 1;
1 2 Customer details live in customers. Orders keep only the customer's id, not a copy of their name and email.
Copies of Ada's email: 3 in the flat sheet, 1 in customers.
3 4 5
In production
Tools and technologies
Primary and foreign keys work the same way in every relational database.
Not tied to one language — use these names when you search docs or design a system.