Relationships and normalisation
Database design starts with one question: how are things related? That is exactly what an ERD captures.
One-to-one: each employee has one access badge, and each badge belongs to one employee.
One-to-many: a department has many employees; an employee belongs to one department. The foreign key always sits on the “many” side: employees.dept_id.
Many-to-many: an order holds many products, and a product appears in many orders.
A many-to-many relationship cannot be stored directly. We resolve it with a bridge table: one order has many products and one product appears in many orders, so order_items links them with two foreign keys.
Normalisation means splitting data into tables so each fact is stored once. Look at this bad table:
| order_id | customer | city | products |
|---|---|---|---|
| 1 | Al Noor Trading | Riyadh | Laptop Pro 14, 27-inch Monitor |
| 4 | Al Noor Trading | Riyadh | Wireless Router, Network Switch 24-port |
| 13 | Al Noor Trading | Riyadh | Laptop Pro 14, Ergonomic Chair |
The problem: the customer's name and city repeat on every order, and products are crammed into one cell. If the customer moves, you must edit every row, and missing one leaves contradictory data.
| 1NF | One value per cell, no repeating groups. |
| 2NF | 1NF + every column depends on the whole key, not part of it. |
| 3NF | 2NF + no column depends on another non-key column (city depends on the customer, not the order). |
The database we have used is in 3NF, which is what made the joins possible. In the next phase we build the ERD Studio, where you draw these relationships and get SQL generated automatically.