The third normal form (3NF)

The third normal form deals with transitive dependencies. A transitive dependency between database fields exists when the value of a non-key field is determined by the value of another non-key field. A database is normalized to the third normal form when

• The table is in second normal form
• The table contains no transitive dependencies

The Dutch client table below contains a transitive relationship. (Dutch means 'from the Netherlands')

In this table not all fields are solely dependent on the primary key. There exists a separate relationship between the postal_code field and the city and province field. In the Netherlands, city and province are both determined by the postal code, so there is no need to store city and province in the clients table. Such transitive relationships should be avoided of you want to model your database to the third normal form.

In this case, removing the transative relationship from the table can be achieved by removing the city and province fields from the table and storing them in a separate table, containing the postal code (primary key), the province name and the city name. Figuring out the postal code - city - province combinations for an entire country is really hard work. That is why such tables are sold commercially.

Another example of the application of the third normal form is this (way too) simple order table from an online shop.

Value Added Tax is a percentage that is added to the price of a product (19% in the table above). This means that the total_ex_vat amount can be calculated from the total_inc_vat amount and vice versa. You should store either one of these fields, but not both. You should leave the task of calculating total_inc_vat from total_ex_vat or vice versa to the program that uses the database.

The third normal form basically says you should never store data that in fields that can be derived from other (non-key) fields in a table. Especially in the client table example, applying the third normal form requires either a lot of work or the purchase of a commercial postal code-city-province table. The third normal form is not always adhered to in database design. When designing a database you should always compare the advantages of a higher normal form to the work it takes to apply and mantain that normal form. In the case of the client table I would personally choose not to normalize to the third normal form.