IntermediateBackend Notes

Database Normalization: 1NF to 3NF, With the Anomaly Each Step Fixes

Normalization rules are easy to memorize and hard to apply, because nobody tells you which specific bug each normal form actually prevents. Here's the anomaly behind each step.

Hen HeangJuly 11, 20268 min read

PostgreSQLSQLDatabase

"Remove partial dependencies" means nothing until you've seen the bug it prevents. Normalization has four concrete failure modes it's fixing, and once you can name the anomaly, the rule stops being abstract.

The four anomalies normalization prevents

Anomaly	What actually happens
Data redundancy	The same fact (a customer's address) is repeated on every row that references them, wasting space and inviting drift.
Insertion anomaly	You can't record a fact (a new subject exists) without also fabricating an unrelated fact (a student who takes it).
Update anomaly	A value is duplicated across rows; updating it in one row but not the others leaves the table self-contradictory.
Deletion anomaly	Deleting the last row referencing something (the last student in a subject) accidentally deletes the subject itself.

1NF: no repeating groups, atomic values

Not 1NF -- repeating group in one cell

-- enrol_id | student_name | subject
-- 102      | Maria        | C++, Java

1NF -- one subject per row

-- enrol_id | student_name | subject
-- 102      | Maria        | C++
-- 102      | Maria        | Java

Cramming "C++, Java" into one cell means you can never write a clean WHERE subject = 'Java' — you'd have to pattern-match a comma-separated string. One value per cell is the whole rule.

2NF: remove partial dependencies on a composite key

This only matters when the primary key is composite (more than one column). If student_name depends only on student_id — not on the full (student_id, subject_id) pair — that's a partial dependency, and it's what causes the anomaly.

Before -- composite PK (student_id, subject_id)

-- student_id | student_name | subject_id | subject | scores | country_code | country
-- 101        | Robert       | 1          | Java    | 70     | US           | United States
-- 102        | Maria        | 1          | Java    | 79     | US           | United States
-- 102        | Maria        | 2          | C++     | 58     | US           | United States

Warning

student_name, country_code, and country depend only on student_id — not on the composite key. subject depends only on subject_id. Both are partial dependencies, and they're why deleting Alex's one enrollment row loses the fact that Alex exists at all.

After -- split into student, subject, student_subject

create table student (
  student_id int primary key,
  student_name text,
  country_code text,
  country text
);

create table subject (
  subject_id int primary key,
  subject text
);

create table student_subject (
  student_id int references student(student_id),
  subject_id int references subject(subject_id),
  scores int,
  primary key (student_id, subject_id)
);

Why phone numbers and national IDs make bad primary keys

A primary key needs to be stable and always available — a phone number can change, and you can't guarantee a national ID format is unique across every country you might onboard a user from. Both are data, not identity. Use a surrogate key (auto-increment integer or UUID) and keep the phone number or national ID as a regular unique-constrained column instead.

Best Practice

In most real projects, 3NF is enough to eliminate the anomalies that actually bite you. Sometimes a table gets deliberately denormalized afterward to avoid an expensive join — that's a conscious performance tradeoff, not a normalization mistake.

REST API Design: DTOs, Exception Handling, and CORS

PL/pgSQL Control Flow: IF, CASE, and LOOP in a DO Block

Advanced

Backend Notes