Skip to content
notes.vinhnq.com

Search

Database & ORMMid20 min read

Databases: normalization from 1NF to 5NF, and denormalization

Review 19 cards
On this page

Databases series: 1. Tables and basic SQL · 2. ERD, joins, GROUP BY · 3. Normalization · 4. Library exercise · 5. Indexes and query performance · 6. Transactions and locking

This post answers how to split data into tables correctly, so that one fact doesn’t live in several places and drift apart. Afterwards you’ll be able to look at a table, say which normal form it’s in, decompose it, and know when to denormalize on purpose. It helps to read tables and primary keys and ERDs and foreign keys first; practise right after with the library system exercise (SQL runs on MySQL 8.4).

1. Why normalize: three kinds of anomaly

Key idea: when one fact is stored in many rows, updates, inserts and deletes can all corrupt the data. Normalization splits tables so each fact lives in one place.

An “everything in one table” design for orders:

order_id customer_name customer_phone product_id product_name qty
1 Lan 0901 P1 Keyboard 1
1 Lan 0901 P2 Mouse 2
2 Lan 0901 P1 Keyboard 1
  • Update anomaly: Lan changes her phone number and you must update 3 rows. Miss one and the data contradicts itself.
  • Insert anomaly: you want to add “Monitor”, which nobody has bought yet, but there’s no order_id to put in.
  • Delete anomaly: Mouse only appears in order 1, so deleting order 1 loses “P2 is called Mouse”. Deleting all of a customer’s orders loses their phone number.

The foundation: functional dependencies

Key idea: every normal form is a rule about which columns may determine which, so you start by listing the functional dependencies.

  • Functional dependency A → B: knowing A determines exactly one B. For example product_id → product_name, zip_code → city.
  • Superkey: a set of columns that uniquely identifies a row.
  • Candidate key: a minimal superkey (remove any column and it stops being one). The PK is the candidate key you pick.
  • Prime attribute: a column that’s part of some candidate key.
flowchart LR
  N1["1NF<br/>atomic values"] --> N2["2NF<br/>no partial<br/>dependency"] --> N3["3NF<br/>no transitive<br/>dependency"] --> BC["BCNF<br/>every determinant<br/>is a candidate key"] --> N4["4NF<br/>no independent<br/>multivalued deps"] --> N5["5NF<br/>no remaining<br/>join dependency"]

Each normal form includes the previous one. 3NF/BCNF is enough for most OLTP systems (transaction processing: many small writes); 4NF and 5NF are rare, but senior interviews sometimes ask.

2. 1NF: one atomic value per cell

Key idea: every cell holds one indivisible (atomic) value, there are no repeating column groups, and every row can be identified by a key.

A violation: several phone numbers crammed into one cell.

user_id name phones
1 Minh 0901111111, 0902222222

The “repeating group” version phone1, phone2, phone3 also breaks the spirit of 1NF: what about someone with four numbers? Finding “who has 0902…” means OR-ing three columns. The fix is a child table:

CREATE TABLE app_user (
  id   BIGINT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);
CREATE TABLE user_phone (
  user_id BIGINT      NOT NULL,
  phone   VARCHAR(15) NOT NULL,
  PRIMARY KEY (user_id, phone),
  KEY idx_phone (phone),
  FOREIGN KEY (user_id) REFERENCES app_user(id)
);

3. 2NF: no dependency on part of the key

Key idea: 1NF plus every non-key column depends on the whole primary key. It only matters for composite keys; a 1NF table with a single-column PK is automatically in 2NF.

An order-lines table with PK (order_id, product_id):

order_id product_id qty product_name unit_price_now
10 P1 1 Keyboard 1290000
11 P1 3 Keyboard 1290000
  • qty depends on all of (order_id, product_id): fine for 2NF.
  • product_name depends only on product_id (part of the key): a 2NF violation, so the name repeats in every order.
  • The fix: a separate product(product_id, product_name, unit_price); order_item keeps only order_id, product_id, qty.

The slides use the same idea: employees keyed by (employee number, department id); the department name depends only on the department id, so it moves out.

4. 3NF: no transitive dependencies

Key idea: 2NF plus no chain PK → X → Y where X isn’t a key. A non-key column may describe only the key, not another non-key column.

emp_id name zip city province
1 Hai 700000 Thu Duc HCMC
2 Nga 700000 Thu Duc HCMC

emp_id → zip and zip → city, province, so city and province depend on emp_id transitively. Renaming a city means updating every employee there. The fix: split out zip_code(zip, city, province); keep employee(emp_id, name, zip).

Mnemonic: every non-key column describes “the key (1NF), the whole key (2NF), and nothing but the key (3NF)”. (The slides title this section “3NF (First normal form)” by mistake; it should be Third.)

5. BCNF: what the slides leave out

Key idea: for every non-trivial functional dependency X → Y, X must be a superkey. 3NF still allows an exception when Y is a prime attribute; BCNF removes it.

Example (student, subject, teacher): each teacher teaches one subject, and each student takes each subject with one teacher. Candidate keys are (student, subject) and (student, teacher). There’s teacher → subject but teacher isn’t a superkey: 3NF holds (because subject is prime) but BCNF doesn’t.

6. 4NF: multivalued dependencies

Key idea: don’t put two independent sets of values for the same key into one table, because the table is forced to store every combination. Give each set its own table.

A multivalued dependency A ↠ B: each A maps to a set of B values, independent of the remaining columns. A table is in 4NF when it’s in BCNF and has no non-trivial multivalued dependency whose left side isn’t a superkey.

emp skill language
Hai Java English
Hai Java Japanese
Hai SQL English
Hai SQL Japanese

The PK is all three columns, so it’s in BCNF. But to avoid implying something false (that Hai only speaks Japanese while doing SQL), you must store the Cartesian product of skills × languages; one more skill means N more rows. The fix: employee_skill(emp, skill) and employee_language(emp, language).

In practice you get 4NF “for free” if every many-to-many relationship has its own junction table. 4NF violations appear when someone merges two junction tables into one.

The slides’ 4NF example and where their definition is off
  • The slides say “4NF = 3NF + no multivalued dependencies”. Formally the prerequisite is BCNF.
  • Their explanation of multivalued dependencies is a bit muddled. The essential point is two mutually independent sets of values attached to the same key.
  • The slides’ example has the same shape as skills × languages: students, courses and hobbies, where a student’s courses and hobbies are independent, so it’s split into two tables.

7. 5NF: join dependencies

Key idea: split a three-column table into three two-column tables only when a cyclic rule makes the join give back exactly the original. Without that rule, splitting creates fake rows.

5NF (Project-Join Normal Form): 4NF plus every join dependency is implied by the candidate keys. In plain words: if you can split the table and join the pieces back into exactly the original table (no rows lost, none added), and that split doesn’t follow from the keys, the table isn’t in 5NF yet.

Example: agents selling products for companies, with the rule “if agent A represents company C, and A sells product type P, and C makes P, then A sells P for C”.

agent company product
A1 Sony TV
A1 Sony Camera
A1 Canon Camera

Under that rule, the three-column table is the join of agent_company, agent_product and company_product. Keep three columns and every new company for A1 means inserting every combination by hand, which is error-prone; split it and each fact is written once.

The slides’ 5NF example: the missing cyclic rule

The slides (subject, teacher, semester) split the three-column table into three two-column tables but never state the cyclic rule that makes the split lossless. The rule you need: “if a teacher can teach subject X, and the teacher teaches in semester K, and X runs in semester K, then the teacher teaches X in semester K”.

With that rule the split is correct; without it you keep the three-column table (see the pitfall just above). Under that assumption the key is all three columns. The question “next semester John doesn’t know which subjects he’ll teach yet, so how do we store that?” shows that “John can teach X” and “John teaches in semester 3” are separate facts, so they go in separate two-column tables.

8. Denormalization: when to break the rules

Key idea: normalization optimizes for writing correctly, at the cost of joins on read. Denormalize on purpose only with measurements, and every copy needs one source of truth and a sync mechanism.

A fully worked example: booking.total_amount in Airline booking: a double-entry ledger and change history. The total can be computed from booking_line but is stored anyway, because it’s the amount fixed at checkout. All three rules apply: the lines are the source of truth, the header is written in the same transaction with a CHECK, and a periodic reconciliation query must return zero rows.

Normalization and denormalization are placed inside the whole design process (access patterns, table templates, foreign keys, JSON, views) in The data modeling process, and applied to six real problems in Data modeling case studies.

Exercises

Exercise 1: The slides’ five “which normal form is this table in?” quizzes

The five quizzes at the end of the lesson (tables shown as images, described here in words). The general recipe: (1) are all cells atomic, any repeating column groups → 1NF; (2) is the PK composite, does any column depend on only part of it → 2NF; (3) is there a chain PK → X → Y where X isn’t a key → 3NF.

  1. A volunteer registration table Registration(RegistrationID PK, RegistrationDate, VolunteerID, LastName, FirstName, StreetAddress, City, State, Zip, Phone, Email). In the sample data, volunteer 101 (same name, address, phone and email) appears in two registrations.
  2. The same registration idea, but the address has one combined “City, State, Zip Code” column (e.g. Aurora, IL 60505), plus three columns ActivityID1, ActivityID2, ActivityID3.
  3. Invoice(InvoiceNumber PK, Date, PatronNumber).
  4. Booking(BookingID, ModelID, ModelLastName, ModelFirstName, ModelTypeID, TypeDescription, HourlyFee) with composite PK (BookingID, ModelID): one booking can hire several models.
  5. Two tables: Booking(BookingID PK, Date, ClientID FK) and Client(ClientID PK, Name, Phone, Email).
Solution to Exercise 1: the slides’ five quizzes

Quiz 1: 2NF, but not 3NF. Every cell is atomic and the PK is one column, so partial dependencies are impossible. But RegistrationID → VolunteerID → LastName, FirstName, StreetAddress, Phone, Email is transitive: the volunteer’s details are copied into every registration, exactly the repetition for volunteer 101. There’s also Zip → City, State.

Fix: Volunteer(VolunteerID PK, LastName, FirstName, StreetAddress, Zip, Phone, Email) and Registration(RegistrationID PK, RegistrationDate, VolunteerID FK). Splitting out ZipCode(Zip, City, State) is a judgement call: strict 3NF says split, but many systems keep City/State because real postal codes don’t map cleanly one-to-one onto cities.

Quiz 2: not even 1NF. The “City, State, Zip Code” column holds a composite value (filtering by state means slicing strings). ActivityID1..3 is a repeating group: a fourth activity needs a schema change, and “who signed up for activity X” needs an OR across three columns.

Fix: split City, State and Zip into three columns; move activities into RegistrationActivity(RegistrationID, ActivityID, PRIMARY KEY (RegistrationID, ActivityID)). Then apply the quiz 1 fix to reach 3NF.

Quiz 3: 3NF (BCNF too). Single-column PK, and Date and PatronNumber depend only on InvoiceNumber. PatronNumber is an FK to the patron table; storing only the number, not the name, means no transitive dependency.

Quiz 4: 1NF, but not 2NF. ModelLastName, ModelFirstName and ModelTypeID depend only on ModelID, part of the composite key. On top of that, ModelID → ModelTypeID → TypeDescription, HourlyFee is transitive, so fixing 2NF still wouldn’t give 3NF. Fix it straight to 3NF:

  • ModelType(ModelTypeID PK, TypeDescription, HourlyFee)
  • Model(ModelID PK, LastName, FirstName, ModelTypeID FK)
  • BookingModel(BookingID, ModelID, PRIMARY KEY (BookingID, ModelID)), plus Booking(BookingID PK, ...) for the booking’s own details.

A business point: if the rate is locked in at booking time, store agreed_hourly_fee in BookingModel. That’s a historical snapshot, not a violation (like unit_price in order_item in section 3).

Quiz 5: both tables are in 3NF. This is what “already split correctly” looks like: client details live in Client, the booking keeps only ClientID. The quiz checks whether you can recognise a design that’s already fine, instead of always hunting for a violation.

Summary: 2NF, not 1NF, 3NF, 1NF, 3NF. The usual mistake is quiz 1: seeing repeated data and concluding “not 1NF”. Repetition across rows is a symptom of a 2NF/3NF violation; 1NF is only about the values in each cell and repeating column groups.

Exercise 2 (extra practice): five tables of my own, covering BCNF and 4NF

The slides’ quizzes stop at 3NF; these five tables practise the higher normal forms.

  • (a) course(course_id PK, title, tags) with tags = 'java,spring,backend'.
  • (b) enrollment(student_id, course_id, enrolled_at, course_title), PK (student_id, course_id).
  • (c) order(order_id PK, customer_id, customer_email, total).
  • (d) class_room(student, subject, teacher), each teacher teaches one subject, PK (student, subject).
  • (e) restaurant(restaurant, dish, delivery_area), where dishes and delivery areas are independent; PK is all three columns.
Solution to Exercise 2: five tables of my own
  • (a) Not even 1NF: tags holds multiple values. Split into course_tag(course_id, tag).
  • (b) 1NF but not 2NF: course_title depends only on course_id, part of the key. Move it back to course.
  • (c) 2NF (single-column PK) but not 3NF: order_id → customer_id → customer_email is transitive; the email belongs in customer. Exception: if it means “the email that receives this order’s invoice”, it’s a legitimate attribute of the order.
  • (d) 3NF but not BCNF: teacher → subject while teacher isn’t a superkey. Split into teacher_subject(teacher, subject) + student_teacher(student, teacher).
  • (e) BCNF but not 4NF: restaurant ↠ dish and restaurant ↠ delivery_area are two independent multivalued dependencies. Split into restaurant_dish and restaurant_area.

Remember

  • Can you show the three anomalies with the orders table?
  • Can you state 1NF, 2NF, 3NF and BCNF in one sentence each, and give a table that’s in 3NF but not BCNF?
  • Can you tell duplication apart from a historical snapshot (purchase price), and a 1NF violation apart from data repeated across rows?
  • Do you know when to split for 4NF/5NF: only for independent sets, or under a cyclic rule?
  • Can you explain the three rules for denormalizing: measure first, one source of truth, write down why?

Interview questions

Junior

What is normalization and why do we do it?

It’s organizing tables so each fact is stored once, based on functional dependencies.

The goal is to reduce redundancy and avoid update/insert/delete anomalies, so the data can’t contradict itself when it changes.

State 1NF, 2NF and 3NF in one sentence each.

1NF: one atomic value per cell, no repeating groups. 2NF: plus every non-key column depends on the whole key. 3NF: plus no transitive dependencies.

Transitive means a non-key column depends on the key through another non-key column.

Can a table with a single-column PK violate 2NF?

No, because a partial dependency needs a key of two or more columns.

A 1NF table with a single-column PK is automatically in 2NF, though it can still violate 3NF.

What’s wrong with storing tags as “a,b,c” in one column?

It breaks 1NF; use an item_tag(item_id, tag) table instead.

You can’t put an FK or uniqueness on individual tags. Searching by tag needs LIKE '%b%' (no index, and it also matches “bb”). Adding or removing a tag means parsing strings.

A table has columns phone1, phone2, phone3 (or ActivityID1..3). Which normal form does it break? What about data repeated across rows?

Numbered columns are a repeating group, which breaks 1NF. Data repeated across rows isn’t a 1NF problem; it’s the symptom of a 2NF or 3NF violation.

  • Repeating group: a fourth item needs a schema change, and searching by value needs an OR across columns. Fix it with a child table (owner_id, value).
  • Repeated data (the same person’s name and address in several rows): it depends on some other column rather than the key (partially or transitively), so it belongs in its own table.

Mid

How does 3NF differ from BCNF? Give an example.

BCNF requires every determinant (left side of a functional dependency) to be a superkey; 3NF makes an exception when the right side is a prime attribute.

Example: (student, subject, teacher) with teacher → subject is in 3NF because subject belongs to the key (student, subject), but not BCNF because teacher isn’t a superkey.

What is a multivalued dependency, and when is 4NF violated?

A ↠ B holds when each A maps to a set of B values independent of the other columns. 4NF is violated when one table holds two independent sets for the same key.

Example: employee × skill × language, which forces you to store the Cartesian product. The fix is one table per set.

Is storing unit_price in order_item denormalization?

No, if it means “the price at purchase time”.

That’s a historical fact that depends on the order line, different from the current price in product. If you don’t store it, changing a product’s price corrupts every old invoice.

Does normalization make a system slower?

Usually not: writes get faster, and reads need extra joins, but joins on indexed keys are cheap in OLTP.

Writes are faster because there’s one place to update, narrower rows and fewer indexes per table. Real slowness shows up only in big aggregates or very hot read paths; that’s when you denormalize selectively, based on measurements rather than guesses.

Senior

When do you deliberately denormalize, and how do you keep the data correct?

When measurements show joins or aggregates are the bottleneck on a hot read path; the copy always has one source of truth and an explicit sync mechanism.

  • Example: counting comments every time a feed loads.
  • Sync in the same transaction with an atomic increment (SET c = c + 1), or asynchronously via outbox/CDC if eventual consistency is acceptable.
  • Add a periodic reconciliation job to fix drift, and document the decision.
Does 5NF matter in practice?

Rarely; it only comes up with cyclic business rules (if A–B, B–C and C–A all hold, then A–B–C holds).

The practical lesson: splitting a three-way table into three two-way tables is only correct if that rule exists; otherwise joining back produces spurious rows. Decide from the business rules, not from sample data.

How does a normalized OLTP schema differ from a reporting/OLAP schema?

OLTP normalizes to 3NF/BCNF for many small writes that must be correct; OLAP uses a denormalized star/snowflake schema for big scans.

  • OLTP: index for point queries.
  • OLAP: fact table plus dimensions, usually in a columnar store in a separate warehouse fed by ETL/CDC.
  • Mixing both workloads on one database lets heavy reports steal locks and I/O from transactions.
A 50-million-row production table violates 3NF. How do you split it without downtime?

Use expand–contract (parallel change) over several deploys, with every step reversible.

  1. Create the new table, which nothing reads yet.
  2. Make the code write to both the old column and the new table.
  3. Backfill old data in small batches (a few thousand rows each, with pauses) so you don’t hold long locks or make replicas lag.
  4. Reconcile the two sources.
  5. Switch reads to the new table behind a feature flag so you can switch back.
  6. Stop writing the old column, and only then DROP it.

For big ALTER TABLEs on MySQL, use online DDL (ALGORITHM=INPLACE/INSTANT) or gh-ost/pt-online-schema-change.

Useful phrases for interviews

  • “Before normalizing, I’d list the functional dependencies, because every normal form is defined in terms of them.”
  • “This column depends on only part of the composite key, so it violates second normal form.”
  • “City depends on the key only through the zip code, which is a transitive dependency, so I’d move it to its own table.”
  • “I usually aim for third normal form or BCNF and denormalize only when I can point to a measured bottleneck.”
  • “That’s not redundancy; it’s a historical snapshot of the price at the time of purchase.”
  • “Every denormalized copy needs a single source of truth and a clear way to keep it in sync.”
  • “I’d split that table with an expand–contract migration: add the new table, dual-write, backfill in batches, switch reads, and only then drop the old column.”
  • “A decomposition has to be lossless: joining the pieces back must give exactly the original rows, no more and no fewer.”

Notes based on the Fundamentals of Backend course (Ronin Engineer), rewritten in my own words.