Relational Databases Explained

MySQL is a relational database. The word “relational” comes from relations, a mathematical word for tables. The big idea is simple:

Store each kind of thing in its own table, and connect the tables with keys.

The problem with one big table

Imagine a shop that records orders in a single table:

Maya’s name and city are written twice. If she moves, you must fix every row, and if you miss one, the data disagrees with itself. The price of a notebook is also repeated.

The relational solution

Split the data into separate tables, one per thing, and link them.

customers

orders

Maya now appears once. Her orders point to her with customer_id = 1.

Keys: the glue

• A primary key is a column (here id) whose value is unique for every row. It identifies a row, like a roll number.
• A foreign key is a column (here customer_id) that holds the primary key of a row in another table. It is the link.

You can follow the link in SQL with a JOIN. Here is a small preview. Do not worry about the details yet; the JOIN chapter explains every part.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

CREATE TABLE customers (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    city VARCHAR(50)
);

CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT,
    product VARCHAR(50),
    price INT
);

INSERT INTO customers VALUES (1, 'Maya Shah', 'Pune'), (2, 'Rohan Verma', 'Delhi');
INSERT INTO orders VALUES (1, 1, 'Notebook', 80), (2, 1, 'Backpack', 999), (3, 2, 'Notebook', 80);

SELECT customers.name, customers.city, orders.product
FROM orders
JOIN customers ON customers.id = orders.customer_id;

1
2
3
4
5
6
7
8

+-------------+-------+----------+
| name        | city  | product  |
+-------------+-------+----------+
| Maya Shah   | Pune  | Notebook |
| Maya Shah   | Pune  | Backpack |
| Rohan Verma | Delhi | Notebook |
+-------------+-------+----------+
3 rows in set

The query started from the orders and looked up each customer through the key. The customer’s data is stored once, and it is combined with the orders only when you ask.

What you gain

• No repeated data. Change a city in one place.
• Consistency. The database can refuse an order for a customer that does not exist (a foreign key rule).
• Flexible questions. Because the data is in clean tables, you can combine it in ways you did not plan for.