Contents

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. For a database to be in the third normal form it must first be in the second normal form.

Transitive dependencies

Rule: there can be no transitive dependencies between fields in a table.

The client table (my clients are dutch and french soccer players) below contains a transitive relationship.

Client

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. If you know the postal code, you already know the city and province.

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.

Order

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 not 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. In the latter case, I would. Storing derived data, like the result of a calculation that is based on existing data is usually a bad idea.