Team LiB
Previous Section Next Section

Third Normal Form

A relation is in third normal form if it is in second normal form and in addition all non-key attributes are mutually independent. Let's take the example of a company that has a single salesperson in each state. Given the relation shown in Figure 2-17, there is a dependency between Region and Salesperson, but neither of these attributes is reasonably a candidate key for the relation.

Figure 2-17. Although Mutually Dependent, neither Region nor Salesperson Should Be a Candidate Key

It's possible to get really pedantic about third normal form. In most places, for example, you can determine a PostalCode value based on the City and Region values, so the relation shown in Figure 2-18 is not strictly in third normal form.

Figure 2-18. This Relation Is Not in Strict Third Normal Form

The two relations shown in Figure 2-19 are technically more correct, but in reality the only benefit you're gaining is the ability to automatically look up the PostalCode when you're entering new records, saving users a few keystrokes. This isn't a trivial benefit, but there are probably better ways to implement this functionality, ones that don't incur the overhead of a relation join every time the address is referenced.

Figure 2-19. These Two Relations Are in Third Normal Form

As with every other decision in the data modeling process, when and how to implement third normal form can only be determined by considering the semantics of the model. It's impossible to give fixed rules, but there are some guidelines: You should create a separate relation only when

  • The entity is important to the model, or

  • The data changes frequently, or

  • You're certain there are technical implementation advantages

Postal codes do change, but not often; and they aren't intrinsically important in most systems. In addition, a separate postal code table is impractical in most real-world applications because of the varying rules for how postal codes are defined.

    Team LiB
    Previous Section Next Section