First, Know What You Don’t Know

This post goes under “Doing it Wrong”, and it describes a situation where a data model… got out of hand.

A colleague of mine recently said, “When developing a new software product the first 6 months of code are stellar. The team is really focused on getting everything as right as possible. After that first six months the pressure is on to produce something and start making money. Overnight the focus changes to the delivery date and every corner is cut.” No truer words were spoken of the project that is the subject of this article. In the beginning there was a dedicated team for the design and development of the data model to be implemented in SQL Server cobbled together from the more senior developers. This team had complete control over the data tier. They performed their duties with diligence, creating volumes of documentation and naming standards.

One small challenge, however, was that this team didn’t actually have the skills required for such an undertaking. This deficiency manifested itself in the reliance on a renowned modeling tool that was originally designed for Oracle. This tool included SQL server support in later revisions, but it was SQL Server support written by Oracle experts. One of the “features” of this product was automated generation of indexes for parents and children of Foreign Key relationships. The output of this feature created non-clustered indexes for clustered indexed fields. There are several Knowledgebase articles on MSDN explaining why this is a worst practice, but I’ll summarize by saying, “unresolvable deadlocks under load”. Oracle doesn’t have clustered indexes; in fact there are several differences between Oracle and SQL Server that drive professionals to specialize in one product or the other and many skills do not translate between the two platforms.

Then came crunch time; having been perceived as the “bottle-neck”, the data tier team was broken up. Each member joined a different development team where it was assumed they would perform the same function as before, but concentrated on their team’s realm of responsibility. Now there was no more coordination of the data model design and each team was given full reign to create whatever entities they thought they needed. The naming standards were abandoned, the index issues propagated through “cut and paste”, data duplication ran amuck, and you name it.

Well, actually none of this was any issue really, until the company wanted to go after larger clientele. This monstrosity of a data model worked perfectly for small clients. In fact, no one took any issue with running some of the most poorly written custom reports ever conceived against the transaction database during business hours, until that large client signed the bottom line.

Suddenly, the application was not performing well. The product could not service the demands of a large customer. What should this company do?

They still had the chance to do what they should have done in the beginning. Involve some real expertise, either by hiring more knowledgeable technical resources, or working with a consulting team to get the product started in the right direction.

After three years of unstructured, unguided software development, the issues aren’t going to be resolved by the team basically capable of layering on more weak bandages and spaghetti code. My colleague again, “It doesn’t matter how much you tune it or modify it; you’re not going to get a sub-compact to perform like a Formula 1. If you have, you’ve replaced so much of the original that you should have just bought the better car to start with.”

Many enterprises aren’t concerned with the long term effects of making short term staffing decisions. Focus is usually placed on large head count over high talent. Highly talented development professionals not only have a higher level of quality output, but quantity as well. Such resources will already know the answers or the correct solutions to challenges unseasoned employees will generally research and then make the wrong choices on.

Minimally, recruiting a devoted product architecture team will set new efforts ahead in the game just by relieving the burdens of keeping up with new technologies from the development staff at large. A qualified SQL Server professional would have spotted the index issue immediately. That same professional would have been able to design a data model that would allow for the growth of features without the expediential growth of data model entities in such a way that product delivery timelines aren’t jeopardized.

Notice also I’m speaking of a SQL Server professional. If even within the realms of database experts individuals tend to specialize a particular platform. Is it not reasonable that someone who excels at writing compiled code is not going to naturally have the same success in developing data models? It has been my experience that even the best web developer will struggle to keep up with a mobile applications developer developing mobile applications. Additionally, neither resource is highly skilled at writing the best formed Transact SQL queries. Trying to form a data architecture team out of developers who haven’t specialized in data architecture is a losing proposition.

In summary, pick the right tool for the job from the start; be that a modeling tool focused on the platforms you want to use or a professional specializing in those same technologies. If the staffing cost seems too much at first, you might have a great opportunity for a temporary jump start form a team of talented consultants. In all honesty it will be cheaper than trying to turn a sub-compact into a Formula 1 race car.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s