Third Normal Form

Third Normal Form (3NF) is a well-known methodology for structuring data with the primary purpose of making it more maintainable by removing duplication.

Definition & Explanation

Third Normal Form was defined by E.F. Codd in 1971 and builds on the First and Second Normal Forms.

First: Requires that a table must have a key, a unique value or value combination that describes that entire record or row. A key can be multiple columns.

Second: Requires that every field in a table must depend upon the entire key. This forces any field that is only dependant upon a portion of the key or keys fields out of the current table.

Third: Requires that any field must be dependant upon ONLY that key. Any field that has an additional dependency on another field must be placed in another table.

Although these rules may sound overly demanding and particular, they help force the process of carefully thinking through the data structure and relationships. The result is a data structure that is very maintainable and very relevant to the business.

To gain a better understanding of Third Normal Form please see:

Advantage

The advantage of a Third Normal Form data structure is that it allows for the meaningful use of field constraints and table triggers to help ensure that various business rules are enforced. This is because a specific field in a specific table will usually line up with a very specific element that a business cares about. For example, a customer's first name is likely to be found in a field named FirstName in a table named Customer. This means that it is very easy to create a rule in the database which ensures that a customer's first name is always populated.

A significant benefit of the Third Normal Form data structure is that it usually results in a structure and data that are intuitive to understand and maintain. You are likely to see individual tables that represent the major elements your business cares about. You likely have a table containing your customers, a table listing your products, a table showing your sales. The relationships between these aspects of your business will be clearly defined and implemented.

dbFront understands these database rules and will enforce them. If the rules are more complex then dbFront purposely delegates the validation to your database of choice. If a user attempts to break a rule, the expectation is that the DBA will have coded a trigger or constraint which will provide the user with a helpful error message. dbFront will show that message in all its glory.

Four, Fifth and Sixth Normal Forms

Beyond Third Normal, there are additional forms of data normalization named Fourth, Fifth and Sixth Normal Form.

It would be natural to assume that these higher normalization standards are better but this is not always true. Where the Third Normal Form results in a data structure that is intuitively meaningful and natural to work with, the higher forms of normalization can result in data structures that are difficult to understand from a business perspective and difficult to work with, even for a DBA.

For this reason, most systems are built to Third Normal Form and the higher forms of normalization are usually reserved for highly specialized environments or highly customizable 3rd party systems.