Database normalisation organises data so that each fact is stored in exactly one place. You apply normal forms step by step: 1NF (atomic values, no repeating groups), 2NF (no partial dependence on a composite key), 3NF (no dependence on non-key columns) and BCNF (every determinant is a key). The result avoids anomalies where updating, inserting or deleting data causes inconsistencies.

What problem does normalisation solve?

Imagine a coaching institute keeps everything in one spreadsheet-like table:

enrolments (unnormalised)
student_idstudent_namephonecourse_codecourse_namementormentor_email
1Asha98xxx11111, 98xxx22222ML01Machine LearningRahulrahul@…
1Asha98xxx11111, 98xxx22222DB01DatabasesNehaneha@…
2Ravi97xxx33333ML01Machine LearningRahulrahul@…

This causes three classic anomalies:

  • Update anomaly: if Rahul's email changes, you must update every row he appears in; miss one and the data contradicts itself.
  • Insert anomaly: you can't record a new course until a student enrols in it.
  • Delete anomaly: if Ravi leaves and you delete his row, and he was the only student in a course, you lose the course and its mentor too.

What is First Normal Form (1NF)?

A table is in 1NF when every cell holds a single (atomic) value, there are no repeating groups of columns, and each row is unique. The phone column above breaks 1NF because it holds two numbers. Fix: move phones to their own table, student_phones(student_id, phone), with one row per number. Now you can search, validate and add numbers properly.

What is Second Normal Form (2NF)?

2NF applies when a table has a composite key. In enrolments(student_id, course_code, …) the key is the pair (student_id, course_code). A table is in 2NF if it is in 1NF and every non-key column depends on the whole key, not just part of it. Here, student_name depends only on student_id, and course_name only on course_code: partial dependencies. Fix: split into students(student_id, student_name), courses(course_code, course_name, mentor, mentor_email) and enrolments(student_id, course_code).

What is Third Normal Form (3NF)?

A table is in 3NF if it is in 2NF and no non-key column depends on another non-key column. In courses, mentor_email depends on mentor, not on the course: a transitive dependency. Fix: create mentors(mentor_id, name, email) and store mentor_id in courses. Now Rahul's email lives in exactly one row.

Normal formRule (simplified)Fix in our example
1NFAtomic values, no repeating groupsPhones into their own table
2NFNo partial dependency on a composite keySeparate students, courses, enrolments
3NFNo transitive dependency between non-key columnsSeparate mentors from courses
BCNFEvery determinant is a candidate keyHandles rarer overlapping-key cases

What is BCNF?

Boyce–Codd Normal Form is a slightly stricter 3NF. It requires that whenever one set of columns determines another, the determining set is a candidate key. Violations are uncommon and usually involve tables with several overlapping candidate keys, for example if each mentor teaches only one course, but a course can have several mentors, and the table is keyed on (student, course). Most well-designed 3NF schemas are already in BCNF.

How does the design change, step by step?

Places Rahul's email is stored in our example as we normalise (with 3 enrolments)
Unnormalised2
After 2NF (in courses)1 per course
After 3NF (in mentors)1

Counted from the example. With 10,000 enrolments, the unnormalised design would repeat it once per enrolment in Rahul's courses.

The final schema has five small tables: students, student_phones, courses, mentors and enrolments. It needs joins to answer questions (see SQL joins explained), but every fact is stored once.

What are functional dependencies?

Normal forms are defined using functional dependencies: A → B means "if you know A, you know B". student_id → student_name; course_code → course_name; mentor → mentor_email. Listing the dependencies in your data is the most reliable way to normalise: each dependency suggests a table whose key is the left-hand side. Interviewers often give a table and its dependencies and ask you to identify the highest normal form it satisfies.

When should you denormalise on purpose?

Normalisation optimises for correct writes. Some systems care more about fast reads:

SituationTypical choiceWhy
Transactional app (orders, payments, enrolments)Normalised (3NF)Correctness, no anomalies
Analytics warehouseStar schema (denormalised dimensions)Fewer joins for reports
Dashboard countersStored totals updated on writeAvoid recomputing on every page view
Document databases (MongoDB)Embed data read togetherOne read per page; see MongoDB vs PostgreSQL
ML feature tablesWide, flat tablesModels need one row per example

The rule: normalise first, then denormalise deliberately where measurements show you need it, and document how copies stay in sync.

Why does normalisation matter for data science and AI?

Most training data comes from normalised production databases. Building a dataset means joining those tables back into one wide table, and understanding the original design is what lets you do it without duplicating rows or mixing up entities. Poorly designed source tables, with anomalies and contradictions, become noisy labels and wrong features. Good schema design upstream is quietly one of the biggest contributors to model quality.

What do keys have to do with normal forms?

Normalisation depends on clearly understood keys. A primary key uniquely identifies each row, such as student_id. A candidate key is any column set that could serve as the primary key; an email column might be one. A composite key uses several columns together, such as (student_id, course_code) in an enrolments table. A foreign key points to a key in another table and lets the database enforce that every enrolment refers to a real student and a real course.

Many teams use surrogate keys, auto-generated numbers or UUIDs, rather than natural keys like phone numbers, which can change. Either way, define foreign keys and unique constraints in the database itself, not only in application code. The database then rejects orphaned or duplicate data even when a bug slips through, which is the practical promise normalisation is meant to keep.

How is normalisation tested in interviews and exams?

Expect questions like: "Here is a table and its functional dependencies; what is the highest normal form, and how would you decompose it?" The method is mechanical once practised. Find the candidate keys, check for multi-valued cells (1NF), partial dependencies on part of a composite key (2NF), and dependencies between non-key columns (3NF). Then decompose so each dependency's left side becomes a table key, and confirm the decomposition is lossless: joining the new tables back must give exactly the original rows, with no extras.

Does normalisation still matter with modern databases?

Yes. Faster hardware and managed cloud databases make joins cheap, but they don't stop contradictory data. Once a customer's address is stored in four tables and updated in three, no amount of speed fixes the inconsistency. Normalisation is about correctness, and that hasn't changed. What has changed is how freely teams build denormalised copies on top, such as search indexes, caches, analytics tables and vector stores for AI, because tooling now makes those copies easier to rebuild from a clean, normalised source of truth.

How do you practise normalisation?

  1. Take a real spreadsheet you use (expenses, a class register) and list its functional dependencies.
  2. Normalise it to 3NF on paper, then create the tables in PostgreSQL or SQLite.
  3. Write the joins needed to rebuild the original view.
  4. Decide which one table you'd denormalise for a dashboard, and why.

Database design, normal forms, joins, indexing, transactions and NoSQL make up Program Zero's five-week database phase. See the SQL and database course or the full curriculum.