1. Technology
You can opt-out at any time. Please refer to our privacy policy for contact information.

Discuss in my forum

Normalizing Your Database: Third Normal Form (3NF)

Putting a Database in Third Normal Form

By

Tropical office
Stickney Design/Moment Open/Getty Images

Third normal form (3NF) is a database principle that allows you to cleanly organize your tables by building upon the database normalization principles provided by 1NF and 2NF.

There are two basic requirements for a database to be in third normal form:

Imagine that we have a table of widget orders that contains the following attributes:

  • Order Number
  • Customer Number
  • Unit Price
  • Quantity
  • Total

Remember, our first requirement is that the table must satisfy the requirements of 1NF and 2NF. Are there any duplicative columns? No. Do we have a primary key? Yes, the order number. Therefore, we satisfy the requirements of 1NF. Are there any subsets of data that apply to multiple rows? No, so we also satisfy the requirements of 2NF.

Now, are all of the columns fully dependent upon the primary key? The customer number varies with the order number and it doesn't appear to depend upon any of the other fields. What about the unit price? This field could be dependent upon the customer number in a situation where we charged each customer a set price. However, looking at the data above, it appears we sometimes charge the same customer different prices. Therefore, the unit price is fully dependent upon the order number. The quantity of items also varies from order to order, so we're OK there.

What about the total? It looks like we might be in trouble here. The total can be derived by multiplying the unit price by the quantity, therefore it's not fully dependent upon the primary key. We must remove it from the table to comply with the third normal form. Perhaps we use the following attributes:

  • Order Number
  • Customer Number
  • Unit Price
  • Quantity

Now our table is in 3NF. But, you might ask, what about the total? This is a derived field and it's best not to store it in the database at all. We can simply compute it "on the fly" when performing database queries. For example, we might have previously used this query to retrieve order numbers and totals:

 SELECT OrderNumber, Total
 FROM WidgetOrders
 

We can now use the following query:

 SELECT OrderNumber, UnitPrice * Quantity AS Total
 FROM WidgetOrders
 

to achieve the same results without violating normalization rules.

If you'd like to ensure your database is normalized, explore our other articles in this series: If you want to receive notifications of new database articles posted on this site, Subscribe to our newsletter
Related Video
Learn About Databases
Microsoft Excel Sort Feature
  1. About.com
  2. Technology
  3. Databases
  4. Design
  5. Normalizing Your Database: Third Normal Form (3NF)

©2014 About.com. All rights reserved.