Database Tips: Normalize Repeating Fields

Formal database design techniques can be described under the general heading of “database normalization.” Good database designers either follow “3rd Normal” database form or break the rules for very important reasons.

One of these normalization rules is to remove repeating fields. If you design your customer table with fields for postal address and delivery address, you have limited your application to only two alternate address values.

A more flexible design would create an address table to allow for companies with more than one postal or delivery address. You could also include other address record types in the system.

An example of a too-rigid design is an insurance claims table developed in MS Access. The original table had fields for four payment dates and amounts. Within a month, this had expanded to six fields. When we saw it six months, the table was being used with multiple records to cover those transactions with more than six values. A more flexible approach would have been to have a payments table with a record for each payment. That would efficiently handle any number of transactions.

Have a look at your table design. Are there any repeating fields that would work better in a subsidiary table?

This article is copyrighted by Crystalkeen, Mindconnection, and Chelsea Technologies Ltd. It may be freely copied and distributed as long as the original copyright is displayed and no modifications are made to this material. Extracts are permitted. The names Crystal Reports and Seagate Info are trademarks owned by Business Objects.