In the second of this five part database design series, we continue to explore improvements to the database schema. These improvements will help create more flexible software, reduce maintenance and support costs, and improve scalability. The second principle of database design is to eliminate redundant data.
Redundant data is a corollary to repeating groups, and often the more dangerous of the two database design problems. While repeating groups make maintenance more difficult and result in slower software, redundant data can cause serious data integrity issues.
A basic example of redundant data can be shown in the following table:
- Author ID
- Author Name
- Author Address
- Author City
- Author State Abbreviation
- Author State Full Name
The “State Full Name” column is redundant with “State Abbreviation”, since there is always a one-to-one mapping between the two. If a user changes the state abbreviation, but not the state full name, this will cause data integrity problems. These data integrity problems often require manual verification of each affected row. Bugs can even surface where one column will be changed and not the other, leading to serious issues down the road.
The solution to this dilemma is to create a separate table for states, and reference the state abbreviation as the key:
Author
- Author ID
- Author Name
- Author Address
- Author City
- Author State Abbreviation
State
- State Abbreviation
- State Full Name
This new schema make sure that any changes to the state will be cohesive throughout the system, and will also help save space in the database by not repeating the state full name on each author record.
Eliminating repeating groups and redundant data are the two basic principles of database design that will help create more maintainable and scalable databases. Join us for the next article in this series, where we further optimize the database by removing columns not dependent on the key.
Written by Andrew Palczewski
About the Author
Andrew Palczewski is CEO of apHarmony, a Chicago software development company. He holds a Master's degree in Computer Engineering from the University of Illinois at Urbana-Champaign and has over ten years' experience in managing development of software projects.
Google+
While working on a custom software development project, it is very important to follow the Rules of Data Normalization. Eventually other people might work later on changes or enhancements to our design and at that stage of software development services it will help a lot.
Steve Magellan
Software Development Chicago