What you'll learn

  1. Quick answer
  2. The problem it solves
  3. The fix, and what it costs
  4. The normal forms, in plain language
  5. When to deliberately not normalize
  6. How to practise it
  7. FAQ

Quick Answer

Normalization means storing each fact in exactly one place. If a course fee is repeated on every student row, changing it means updating many rows and any missed row is now wrong. Split into separate tables and join them.

The problem it solves

Suppose one table holds students and their course details:

roll | name  | course    | fee
1    | Asha  | BTech CSE | 90000
2    | Ravi  | BTech CSE | 90000
3    | Meera | BSc IT    | 60000

The fee for BTech CSE is stored twice. With 500 students it is stored 500 times. Three specific problems follow, and they have names that appear in exams:

  • Update anomaly — the fee changes and you must update every row. Miss one and the database now contains two different fees for the same course, with no way to tell which is right.
  • Insertion anomaly — you cannot record a new course until a student enrols on it, because there is nowhere to put it.
  • Deletion anomaly — the last student on a course leaves, and the course's existence and fee vanish with them.

All three come from the same root cause: one fact stored in more than one place.

The fix, and what it costs

Split into two tables, linked by a key:

CREATE TABLE course (
  id INT PRIMARY KEY, name TEXT, fee INT);

CREATE TABLE student (
  roll INT PRIMARY KEY, name TEXT, course_id INT);

Now the fee exists once. Changing it touches exactly one row:

UPDATE course SET fee = 95000 WHERE id = 1;
-- 1 row updated, regardless of how many students are enrolled

The cost is that reading combined data now needs a join:

SELECT s.name, c.name, c.fee
FROM student s
JOIN course c ON s.course_id = c.id
ORDER BY s.roll;
Asha  | BTech CSE | 90000
Ravi  | BTech CSE | 90000
Meera | BSc IT    | 60000

Same output as the original table — but the duplication is now only in the result, not in storage. That is the whole trade: slightly more work to read, guaranteed consistency to write.

The normal forms, in plain language

The definitions are precise but opaque. Here is what each actually asks for:

  • 1NF — no repeating groups and no lists inside a cell. A phone column containing "9876543210, 9123456780" violates it. Each value is atomic.
  • 2NF — 1NF, plus every non-key column depends on the whole primary key. Only relevant when the key is made of several columns.
  • 3NF — 2NF, plus no non-key column depends on another non-key column. If you store pincode and also city, and pincode determines city, that is a 3NF violation.

The commonly quoted summary is that every non-key attribute must depend on "the key, the whole key, and nothing but the key". For interviews, being able to spot a violation in a sample table matters more than reciting the definitions — that is what gets asked.

3NF is where most real designs stop. BCNF and beyond exist and are worth knowing by name, but they address cases that appear rarely in ordinary applications.

When to deliberately not normalize

Normalization optimises for correct writes. Sometimes reads matter more, and duplication is chosen on purpose.

A reporting dashboard that joins eight tables on every page load may be better served by a pre-joined summary table. Analytics systems do this routinely. The cost is accepted knowingly: duplicated data must be kept in step, usually by rebuilding it on a schedule.

The distinction that matters in an interview is between deliberate denormalization with a stated reason and accidental duplication because nobody thought about it. The first is engineering; the second is the bug at the top of this article.

Related: an index speeds up the joins that normalization introduces — see database indexing explained.

How to practise it

Take any spreadsheet you have — a club register, a fee sheet, a list of orders — and find the column where the same value repeats across rows. That repetition is almost always a separate table waiting to be extracted.

Then ask the three anomaly questions. What breaks if this value changes? Can I add a new one of these without a related record existing? What is lost if I delete the last row? If any answer is uncomfortable, the design needs splitting.

Doing that once on data you understand teaches more than working through textbook examples, because you already know which facts are supposed to be independent.

Frequently Asked Questions

Is 3NF always the right target? For most transactional applications, yes. It removes the common anomalies without making queries unreasonably complex. Go further only when a specific problem calls for it, and denormalize deliberately when read performance genuinely demands it.
Do joins make my database slow? Joins on indexed key columns are fast and are what relational databases are built for. Performance problems usually come from missing indexes or from selecting far more data than needed, not from joining as such.
What is the difference between a primary key and a foreign key? A primary key uniquely identifies a row within its own table. A foreign key is a column that refers to the primary key of another table, which is what links the two and lets the database enforce that the reference is valid.
Does normalization apply to NoSQL databases? The principles apply but the trade-offs differ. Document databases often embed related data deliberately to avoid joins, accepting duplication for read speed. You still need to decide how duplicated facts stay consistent.
How is this asked in interviews? Usually as a sample table with a visible anomaly, followed by 'what is wrong with this design and how would you fix it'. Practise spotting repeated facts rather than memorising the formal definitions.