Q

Which normal form is used most?

SQL expert Rudy Limeback explains what normal form is used in most database projects.

Which normal form is used in most of database projects? Why? Can you explain with examples?

My guess is that most database projects are in third normal form. Why do I say this? Because I have seen thousands of database designs, and most of them are constructed quite well, by conscientious database designers, with due regard for functional dependencies—even though they might not know what "functional dependencies" are, they still manage to do it correctly.

Most SQL tutorials and references suggest that you should strive to attain third normal form. Here's a quick rundown on the first three normal forms:

  1. First normal form (1NF) has two requirements: that there be a primary key, and that no column shall contain more than one value.

  2. Second normal form (2NF) requires that all non-key columns are fully dependent on the entire primary key. If the table has only a single-column primary key, this requirement is easily met.

  3. Third normal form (3NF) requires that there are no transitive dependencies, where one column depends on another column which depends on the primary key.

Normal forms are also inclusive. In other words, to be in 2NF, a design must already be in 1NF, and to be in 3NF it must already be in 2NF. Almost all database designers are trying to achieve 3NF, and most make it. Some consciously denormalize their design for a specific reason, but this occurs infrequently.

And yes, there are even higher normal forms, but very few designers take their designs that far. So the most common normal form is 3NF.

This was first published in July 2008

Dig deeper on Oracle development languages

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close