From: Kwabena Asare, head of operations
Deliverable: four linked tables built from order_export, and a view that rebuilds the export from them
"The old order system only gives us one flat export. Every address change means editing dozens of rows, and we keep finding the same customer spelled two ways. Build us proper tables: customers, products, orders and order lines, with keys so bad data can't get in. Then show me the new tables give back exactly what the export had."
Data: order_export (one row per order line, customer and product details repeated).
How this works: each step starts from the database your previous step left, so build in order. Ids are numbered in a fixed order (emails for customers, names for products) so everyone's ids match.
This project is the normalization lesson done for real. You design the tables, give them keys and constraints, load them from the export with INSERT ... SELECT, and then check your work by rebuilding the original rows with joins.
Load order matters when tables reference each other: customers and products first, then orders (which point at customers), then order lines (which point at orders and products). A foreign key refuses a row whose parent is missing, which is exactly the check you want during a migration.
Each step is checked by the state of the database after your script, and the next step starts from the database you built.