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.
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.
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.
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.