Third Normal Form (3NF) Principles

Third Normal Form (3NF) is all about bringing even more order to our data. In short, a table is in 3NF if:

1. It's already in Second Normal Form (2NF).
2. All non-key attributes depend only on the primary key and nothing else (no transitive dependencies!).

In other words, 3NF requires that all data in the table is directly related to its primary key and doesn't depend on other non-key attributes.

A transitive dependency pops up if attribute A depends on attribute B, and attribute B depends on attribute C, creating a chain of dependencies A → B → C. For example, "Employee" depends on "Department", and "Department" depends on "Location". So "Employee" is transitively dependent on "Location".

Example of a 3NF Violation

Let's say we have an employees table that stores employee data:

Here:

• employee_id — primary key.
• department and department_manager — non-key attributes.

At first glance, everything looks fine, but if you look closer, you'll spot a problem: department_manager doesn't depend on employee_id, but on department. So, there's a transitive dependency: employee_id → department → department_manager.

Problems That Can Happen

If we change the department manager's name ("Finance"), we'll have to update every row where that department is listed. If we forget to update one of the rows, the data gets inconsistent. That's a headache you can totally avoid.

Bringing the Table to 3NF

To get rid of transitive dependencies, we'll split the table into two: one will store employee data, and the other — department info.

employees table

departments table

Now everything's structured, and each table does its own thing:

1. The employees table stores employee data.
2. The departments table stores department info and their managers.

If the department manager changes, we only need to update one row in the departments table, not every employee record.

How Do You Know a Table Breaks 3NF?

To figure out if a table breaks 3NF, check:

1. Are there any transitive dependencies? For example, attribute A depends on attribute B, which depends on attribute C.
2. Do all non-key attributes depend directly on the primary key? If something depends on another non-key attribute, the table isn't in 3NF.

Practical Example: Store

Let's look at a sales table that holds sales data:

Here you can clearly see redundancy: the product price info is repeated for every sale. If the price changes, the data instantly gets inconsistent.

To fix the 3NF violation, we'll split the table into two:

1. The sales table will store sales info.
2. The products table — product data and their prices.

sales table

products table

Now, if the product price changes, we just update one row in the products table, and the data stays consistent.

Hands-on Task

Here's a little challenge: you have a students_courses table like this:

Split the table so it fits 3NF. Hint: you need to create three tables (students, courses, teachers) and link them up the right way.

Why Bother with 3NF?

Why even care about Third Normal Form (3NF)? Because it seriously makes your life easier. When your tables are in 3NF, your data is neater — no extra duplication, so there's less chance of accidentally breaking something. Updates are faster and simpler, since everything's stored in one place, not copy-pasted all over the rows.

Plus, your database structure gets more flexible. Want to add a new field or tweak an old one? No need to overhaul the whole system.

But, like any good story, there's a catch: going too hard on normalization can leave you with a bunch of tiny tables and complex queries with lots of JOINs. Sometimes that can actually slow things down. So the main thing is balance. Normalize where it makes sense, but if a little redundancy makes things easier — don't be afraid of it.

Next up, we'll dig deeper: you'll learn how to set up relationships between tables the right way and build databases that are both handy and reliable. Let's go, on to awesome database design!