Normalization & Schema Design

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

The anomalies

Picture one flat table storing student, department and head-of-department in every row:

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

1NF

Each column holds a single atomic value; no repeating groups.

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

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

Postgres has array and JSONB types that look like a way around this. They are right for genuinely variable, non-relational attributes and wrong as a substitute for a child table you will need to query, join and constrain.

2NF

In 1NF, and every non-key column depends on the whole primary key. Only relevant with 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)

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
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 nearly every application schema.

BCNF and beyond

BCNF requires every determinant to be a candidate key. 4NF separates independent multi-valued facts, and 5NF handles join dependencies. Useful to know for exams and unusual schemas; 3NF plus judgement covers real work.

Denormalization

Duplicating data on purpose is sometimes right:

• A cached comment_count instead of counting on every page load.
• A total_amount stored on an order so a historical invoice does not change when prices do.
• A materialised view for an expensive dashboard query.

The cost is that you now own consistency. Keep the copy in sync with a trigger, a scheduled refresh or application logic, and write down why the duplication exists.

Postgres-specific design notes

• Use schemas to group related tables (billing.invoices, academic.students) rather than prefixing every table name.
• Prefer text over varchar(n) unless the limit is a real rule.
• Use timestamptz, not timestamp, and store UTC.
• Reach for domain types when a rule repeats across tables:

CREATE DOMAIN email AS citext CHECK (VALUE ~ '^[^@]+@[^@]+\.[^@]+$');
CREATE TABLE users (id int, contact email);

• Enums are fine for small fixed lists, but adding a value used to require a lock (it is cheap since Postgres 12), and removing one is still awkward. A lookup table with a foreign key is more flexible.

Design checklist

1. One table per entity; name tables in the plural and columns in snake_case.
2. Small surrogate primary key on each table.
3. Foreign keys for every relationship, with an index on the referencing column.
4. Normalise to 3NF, then denormalise only where a measured problem justifies it.
5. Constraints for every rule you can express in the database.
6. created_at and updated_at on anything you will ever debug.
7. Index what you filter, join and sort on — and nothing else.