++++

Data Engineering

May 2026×11 min read

Learn 1NF, 2NF, 3NF, relationships, and how normalization reduces duplicate data in MySQL schemas.

16. Normalization and Schema Design

Driptanil DattaSoftware Developer

Normalization and Schema Design

Normalization is the process of organizing tables to reduce duplication and avoid inconsistent data.

The problem with one wide table

This table mixes customer and order facts:

CREATE TABLE customer_orders_flat (
  customer_id INT,
  customer_name VARCHAR(100),
  email VARCHAR(100),
  order_id INT,
  order_date DATE,
  total_amount DECIMAL(10, 2)
);

If one customer places five orders, their name and email are repeated five times. If the email changes, every repeated row must be updated.

Split entities into tables

Better design:

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  customer_name VARCHAR(100),
  email VARCHAR(100) UNIQUE
);
 
CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  order_date DATE,
  total_amount DECIMAL(10, 2),
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Now customer details live once, and orders reference the customer.

First normal form

1NF means each field stores a single value and each row is unique.

Bad pattern:

CREATE TABLE students_bad (
  student_id INT,
  course_names VARCHAR(255)
);

course_names might contain "Math, Science, History", which is hard to filter.

Better pattern:

CREATE TABLE student_courses (
  student_id INT,
  course_id INT
);

Second normal form

2NF means every non-key column depends on the whole key. It matters most when a table has a composite key.

Bad pattern:

CREATE TABLE enrollments_bad (
  student_id INT,
  course_id INT,
  course_name VARCHAR(100),
  grade VARCHAR(2),
  PRIMARY KEY (student_id, course_id)
);

course_name depends only on course_id, not the full key. Move it to courses.

Third normal form

3NF means non-key columns should not depend on other non-key columns.

Bad pattern:

CREATE TABLE employees_bad (
  employee_id INT PRIMARY KEY,
  department_id INT,
  department_name VARCHAR(100)
);

department_name depends on department_id. Put departments in their own table.

CREATE TABLE departments (
  department_id INT PRIMARY KEY,
  department_name VARCHAR(100)
);

Practical rule

Model one real-world concept per table: customers, orders, products, students, courses, employees, departments. Use foreign keys to connect them.

15. Transactions 17. Views