Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

SQL Scripts & Samples

Posted Mar 6, 2001

Normalizing an Existing Database - Page 2

By Bruce Szabo

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.



SQL Scripts & Samples Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
SQL Scripts & Samples Forum
Topic By Replies Updated
sql help!! add 1 August 22nd, 11:58 AM
How To Combine These 2 Queries into 1 Query ? tarek_land 1 June 7th, 08:37 AM
solving query svibuk 1 February 3rd, 06:08 AM
converting from a character string to uniqueidentifier saturnius 4 January 4th, 05:56 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date