Database Normalization - Page 5

March 22, 2000

Before you rush off and start normalizing everything, a word of warning. No process is better than good old common sense. Take a look at this example.

Customer table

Number - primary key
Zip Code

What normal form is this table in? Giving it a quick glance, we see no repeating groups, and a primary key defined, so it's at least in 1st normal form. There's only one key, so we needn't even look for partial dependencies, so it's at least in 2nd normal form. How about transitive dependencies? Well, it looks like Town might be determined by Zip Code. And in most parts of the world that's usually the case. So we should remove Town, and place it in a separate table, with Zip Code as the key? No! Although this table is not technically in 3rd normal form, removing this information is not worth it. Creating more tables increases the load slightly, slowing processing down. This is often counteracted by the reduction in table sizes, and redundant data. But in this case, where the town would almost always be referenced as part of the address, it isn't worth it. Perhaps a company that uses the data to produce regular mailing lists of thousands of customers should normalize fully. It always comes down to how the data is going to be used. Normalization is just a helpful process that usually results in the most efficient table structure, and not a rule for database design. But judging from some of the table structures I've seen out there, it's better to err and normalize than err and not!

Additional Resources:

» See All Articles by Columnist Ian Gilfillan