Third Normal Form

What Does Third Normal Form Mean?

The third normal form — or 3NF — is part of a set of concepts for database normalization that also includes first normal form (1NF) and second normal form (2NF).

Techopedia Explains Third Normal Form

These three “normal forms” or normalizations tend to confound people who aren't professional database administrators or mathematicians.

However, here's an easy and simple way to think about third normal form, as well as the two normal forms that precede it.

As you might imagine, much like dimensional complexity, the first normal form is very basic compared to the forms that come after it. That's also true for the second normal form as well.

Here are the basic definitions of each one of these three successive concepts.

First Normal Form (1NF)

The first normal form simply has to do with making sure that each data field holds a single value, and not a composite value or multiple values.

That's fairly easy to understand, looking at a diagram where a data table might, for example, have the following identifiers for table contents — name, phone number, state and country, along with a primary key identifying the record number.

To comply with the first normal form, you go through the whole table and make sure that none of these have multiple values.

Second Normal Form (2NF)

The idea for the second normal form is not quite as straightforward or simple.

The second normal form is defined by experts as “eliminating repeating groups,” and “eliminating any partial dependencies among database relations.”

If that sounds confusing, you can also think about 2NF as an attempt to “reduce redundant data being stored in memory.”

For example, if there is a record in a table identifying a given status for a user on a table, and it is cited 100 times for 100 different users, you don't want to store all of those redundant values separately.

You want to reference the status once instead, and add that to those 100 user accounts. You wouldn’t want to be storing the word “Administrator” in a table of 100 different individual administrators. It’s just not good data hygiene.

So when you go through to compliance with second normal form, you’re reorganizing table relations to make sure that they are fairly independent, in order to achieve this goal.

Third Normal Form (3NF)

Now, here's the definition of the third normal form:

“A relation is in third normal form if there is no transitive dependency for non-prime attributes (and is also in the second form…)”

Here’s the important part: no transitive dependency for non-prime attributes.

Also, in a 3NF-compliant table, no non-primary key attribute has transitively dependent relationships to the primary key.

Again, this has to do with the relations between items in a database table, but it's more complex. Here's an easy way to think about third normal form — it makes sure that these fields will not have anomalies based on changes — administrative inserts, updates, and deletions.

One could say that it preserves lossless database transitions, and that functional dependencies are eliminated.

So in general, it's the process of engineering your data table the right way, so that every value has independence, and your procedural changes don't damage other parts of your data table as you execute them.

That's something that's fairly easy to understand when you're looking at using candidate keys and primary keys to engineer databases this way.

You can also understand the three cascading normal forms in relation to each other — that normalization proceeds according to these steps.

Maybe a system is compliant with first normal form, but not the other two.

However, it won't be compliant only with second or third normal form, because of the prerequisite nature of the set.

So that’s it in a nutshell — again, 3NF means that various parts of the record are independent, so that changes don’t cause unintended consequences.

And that means ordering data differently in relational database designs.