Normalization & Schema Design

Normalization organises tables to remove redundancy and the update anomalies that come with it.

The anomalies it prevents

Imagine one flat table holding student, department and head-of-department in every row:

• Insert anomaly — you cannot record a new department until a student enrolls in it.
• Update anomaly — changing the department head means updating hundreds of rows; miss one and the data contradicts itself.
• Delete anomaly — removing the last student of a department erases the department itself.

First Normal Form (1NF)

Every column holds a single atomic value; no repeating groups or comma-separated lists.

-- violates 1NF
student_id | name   | phones
1          | Rahim  | 017xx, 018xx

-- 1NF
students(student_id, name)
student_phones(student_id, phone)

Second Normal Form (2NF)

In 1NF, and every non-key column depends on the whole primary key — this only matters for composite keys.

-- violates 2NF: course_title depends only on course_id
enrollments(student_id, course_id, course_title, grade)

-- 2NF
enrollments(student_id, course_id, grade)
courses(course_id, course_title)

Third Normal Form (3NF)

In 2NF, with no transitive dependency: a non-key column must not depend on another non-key column.

-- violates 3NF: dept_head depends on dept_code, not on student_id
students(student_id, name, dept_code, dept_head)

-- 3NF
students(student_id, name, dept_code)
departments(dept_code, dept_head)

3NF is the practical target for almost every application schema.

BCNF and beyond

BCNF tightens 3NF: every determinant must be a candidate key. 4NF removes independent multi-valued facts stored in one table, and 5NF handles join dependencies. These matter in exams and in a few unusual schemas; 3NF plus good judgement covers real work.

Denormalization

Deliberately duplicating data to avoid joins is sometimes correct:

• A cached comment_count on a posts table instead of counting on every page load.
• A total_amount stored on an order so a historical invoice does not change when prices do.
• Reporting tables built for dashboards.

The cost is that you now own the consistency problem — keep the copy in sync with triggers, scheduled jobs, or application logic, and document why the duplication exists.

A design checklist

1. List the entities and what each one is — one table per entity.
2. Give each table a small surrogate primary key.
3. Model relationships with foreign keys; many-to-many gets a junction table.
4. Normalise to 3NF, then denormalise only where a measured problem justifies it.
5. Index the columns you filter, join and sort on.
6. Choose the narrowest correct data type for every column.
7. Add created_at / updated_at — you will always want them later.