My reasoning behind the title of this blog entry is to discuss how to be confident in your skill set as an IT professional and to avoid that feeling of “Where am I going or am I doing the right thing with this solution?” Recently I was questioned by a team member on my approach to the database design I architected for my current application to be released to NASA for administration of their award processes. One might think an application geared toward awards administration would be a straight forward project with minimal requirements. This proved not to be the case. The requirements document alone for Phase I if printed could have contributed significantly to the planet’s deforestation.

To dig deeper into proper database design and the questions surrounding why I designed the database in the fashion I did, it became readily prevalent in the conversation with the team member and fellow coder that he didn’t have a full understanding of proper hierarchical approaches to db design and the pitfalls that can be encountered when taking an all encompassing table approach with a type table to differentiate each entry in this table. Not to speak of the issues that would be encountered with deletion of data (multiple delete statements instead of cascading) and self table joins with convoluted where clauses associated with multiple aliases. When designing databases it is important to understand that if the attributes of an entity (that may be thought of as similar but with changing types) differ in count, leaving entries with null values where they don’t apply requires a new entity in the db.

An example of this is organizations and divisions. Proper db design would enforce integrity by creating a table for each of the entities in question. They each have different characteristics other than being a different type and one belongs to the next (parent, child is immediately prevalent here). For coders who are not comfortable with joining tables I can relate with the type approach in question. To make coding easier for them in regard to retrieving data, views should be created. This all falls in-line with the title of this entry. Be a student of your craft. In discussing the issue I knew that he needed to do some reading on the subject and that my responses were not going to change his thinking because he didn’t understand all the benefits a properly architected database can provide. Under the guise of his confusion behind multiple joins to retrieve the data he needed for a lookup he didn’t understand that the type approach would require joins as well to present the hierarchical data, but with a table that would have multiple keys or unique constraints that would hinder editing and deleting of data.

There is something to be said for experience here, but experience can be compensated for by becoming a student of your craft. We’ve all been there when it comes to the “Is there a better way?” question when embarking down undiscovered territory. The only way to compensate for this uncertainty is to research and educate oneself on the issue in question.

(Posted by Jeffrey Bouley from Strikefish)