Normalizing an Existing Database - Page 2

Denormalization

In the design process it became clear the database could be further normalized. Some areas for normalization were the addresses, policies, and options. Also, denormalization could have occured in other areas. The following sections will cover areas where these two processes could have occurred.

Further Normalization

Under the current database design it is possible to have the same address, contact, email, web and phone information for each role in a given firm. This would cause a lot of duplicate data. Knowing in most cases, however that not all this information is repeated for each role lead to the conclusion the database could be denormalized. For example, There may be a returns department address but there is no contact for the returns department. There could, however, be a customer service agent or even multiple agents. For the agents it is possible only a toll free phone number will be kept. In this case a role is created for agent1 or multiple agents and their name is kept in the contact table while the toll free phone number is stored in the number table. In this case address information could be added to the database but it may not be required by the process the database supports.

The policies could also have all been stored in one table. Each policy could have had a descriptive lookup type. It seemed as though it would be easier to have three policy tables for programming purposes rather than one table containing all the policies. When reporting information the data will be able to be kept together by adding or removing tables from the query. In the event all policies are needed a union query will allow the tables to be combined.

The options tables could potentially have been combined to form a more generic classification similar to how policies may have been further normalized. It probably made less sense to further normalize the options as the data seems to fall nicely into the current tables.

Further Denormalization

The address, contact, email, web and phone information could have been further denormalized. It would have been possble to combine these tables into one table. One of the major problems with this denormalization would have been phone numbers. If another phone number type was added it would have required adding a field to the table. Combining the other four tables may have presented less severe problems but would have resulted in a number of blank fields for each record.

The policies could have been added to the respective options table but this again would have meant modifing table structure to accomodate new policies. Trying to avoid modifing table structure while accomodating future data lead to the current table design.

Conclusions

One finds when normalizing and denormalizing there are pros and cons to both approaches. While programming it becomes evident why many databases are denormalized. In some cases the original programming for a denormalized database is easier. Developing programs that make use of normalized data, however, leads to code that is reusable in many more situations. This fact alone is enough to fight through the normalization and denormalization battle.

For another perspective, check out David's comments on the normalization of this database, found on the next page of this article.