Design for MySQLs
MySQL schema design is structuring tables so data is stored once and stays consistent. It uses primary keys, foreign keys, unique and NOT NULL constraints, and normalisation through the first three normal forms. This unit in the Relating Data stage turns a real problem into entities and relationships before any queries are written.
Stage: Relating Data · Part of the MySQL roadmap · Last reviewed 19 November 2026
What to learn in Design
- Pick a primary key and use AUTO_INCREMENT correctly
- Declare foreign keys with ON DELETE actions to protect integrity
- Normalise a flat table into first, second and third normal form
- Model a many-to-many relationship using a junction table
- Decide when denormalising a column is a reasonable trade-off
Design interview questions
What does a foreign key do in MySQL?
A foreign key links a column in a child table to the primary key of a parent, and InnoDB rejects values that have no matching parent row. Actions such as ON DELETE CASCADE or RESTRICT define what happens when the parent row is deleted.
What is database normalisation and why do it?
Normalisation organises data into related tables so that each fact is stored once. It prevents update, insert and delete anomalies, such as a changed address being updated in some rows but not others. The cost is more joins at query time.
Where Design fits in the MySQL roadmap
Design belongs to the Relating Data stage (beginner level). Joins and design.
Other units in this stage
Back to the full MySQL roadmap