In the previous database design post, we discussed isolating independent multiple relationships. The goal was to find relationships in the database that were independent, yet incorrectly bound to the same row. This last and final database design principle helps us find data that is too flexible, and can instead be combined to help reduce database size and optimize speed.
In order to illustrate the concept, let’s take an affiliate bookstore configuration. Each store will have to decide which information to show about each book in their product listing pages.
StoreBookAttributes
- Store ID
- Book ID
- Book Attribute (# pages, author, title, genre, etc.)
Let’s say that each store will have the same attributes on each product listing page throughout their site. If this is the case, the table above is quite inefficient. Every time a new book is added, the database will need to create one additional record for each book attribute. If we show five attributes per book, every book add will require five additional database adds just to make sure that the book attribute information will be displayed.
In addition, if for some reason an intermediate record is deleted, incorrectly added, or improperly updated, the display can become corrupt without any visible signs. A better solution to this scenario would be to separate the table into two:
StoreBooks
- Store ID
- Book ID
StoreBookAttributes
- Store ID
- Book Attribute
This will require only one insert/update/delete statement per book, and will save significant space in the database. As the number of books in the database increases, this can have a large impact on the performance and maintainability of the software.
Altogether, these five database design principles will help software developers create a good database foundation for their software. They help avoid the most common errors and, and will result in a more maintainable, stable, and flexible database on which to build software applications. Of course, once these basic techniques are mastered – the truly hard work begins – understanding all the varied database applications in the field, with both optimizations and exceptions for each industry and class of software.
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+
Acquire the fastest working proxies. Have a look at our web-site.