In the previous series on database design principles, we discussed potential pitfalls in software development and ways to solve common database design mistakes. In the past week, while revamping an ASP.NET software application developed by another company, I have had the opportunity to see many of these rules come to life, and see their malpractice wreak havoc on the enhancements.
The amount of work required to make enhancements to bad software is easily double, if not more, of that required to make enhancements to good software. The problems don’t just stop there – changes to bad software are also more buggy and prone to crashes. As we have been discussing the concept theoretically over the past week, a few practical examples are in store.
The previous software was structured with a four-tiered relationship – great grandfather, grandfather, parent, and child. Since the “grandfather” element could sometimes be irrelevant, the previous developer had allowed parents to link directly to their great grandfather, instead of requiring a “dummy” or “default” element in between. This caused duplication of most functions throughout the site, and overhead further down the pipeline as enhancements were added to the site.
Next, each lower-level table had ALL of the keys of the parent table. The child table kept a link to the Parent ID, Grandfather ID, and Great Grandfather ID. Not only was this a waste of space, in many places the data also became corrupted, leaving questions as to where exactly the child should be attached. The proper format should have been with each lower-level table having a single foreign key to its direct parent.
The poor design continued on to the individual ASP.NET forms. Instead of passing just the single key in the querystring to each form, the “Edit Child” form was passed not just the Parent ID, Grandfather ID, and Great Grandfather ID, but also the Parent Name, Grandfather Name, and Great Grandfather Name. This leads to vulnerability to cross-site scripting attacks, and also a lot of overhead in passing that information to each link. If the developer accidentally forgets to add one of the items to the link, it will result in a hidden error on a difficult-to-test path.
These errors are just scratching the surface of how the poorly designed database design impacted the software development project. When dealing with projects like these, developers are left with a choice of the lesser of two evils – spending significant time reworking the code and going over-budget, or continuing along the poor architecture and creating even more work further down the road. The interesting part is that the client never really knows if software has been built well or poorly, until they are hit with problems years down the road. As much as people dislike the limitations of Building Codes, perhaps a similar code for software developers, combined with “software inspectors”, would result in more maintainable line-of-business applications, and better resulting software projects.
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.