T-SQL Tuesday #83. We're still dealing with the same problems.

This months T-SQL Tuesday is hosted by Andy Mallon at T-SQL Tuesday #83 , the subject is "We're still dealing with the same problems."

In the 20+ years I have been working with MS SQL Server, some issues continue to pop up, miss configured NIC settings, badly configured servers in general, bad disk configurations, horrible use of tempdb, table variables, SQL Variant... just to name a few.

However, in most of the jobs I have had, I come in as the DBA for a system already in use, sometimes for many years.  And many times without there ever having been a person in charge of the database(s).  I end up dealing with pre-existing issues in the architecture of the databases.

The one thing I see over and over, is a complete lack of foresight in design.  Or as I like to call it "Spec Fixation,"  which is where things are developed exactly to specifications, without any thoughts to how the system will be used, or what could be needed in the future.

Two examples are in order I believe.  One worked out, the other, well, still a work in progress.

Example 1:
Working to maintain listener information, such as age groups, for a large nation wide radio company, the developer I was helping was developing it with the 3 buckets hard coded for age groups.  I rejected the design and coached him how to make the buckets table driven.  He kept insisting that is not what the design called for, to which I replied I really did not care what the spec said as long as we met the minimum.  Since I refused to move anything to production that was not table driven, he finally accepted my solution and implemented it.  On the day of the deployment, everything worked the way the business wanted it.  However, by 4 PM, the business manager came to him and basically said "uhm, you know, we really need more fidelity to this data, can you make it 5 buckets instead of 3?"  In 15 minutes of work we had it with the 5 buckets.  Estimated delivery time if we had gone with his original design would have added 2 columns to the table, changes to the front end etc.  Win!

Example 2:
ETL.  Spec said only Address Line 1 is needed to be loaded, so the developers only bring that line in (plus name, city etc.).  Fast forward 8 years, I get a request on my desk: "Please add Address Line 2 to import, and all tables.  Oh, and we need historical data for previously loaded files.  And for all address types".

Groan.
No normalization in this database (which is just one of about 40 databases with SIMILAR structure, but not identical).

2 weeks of research, here is what I found that needed changed:
60 tables
10 views
260+ procedures that reference the above tables and views.
1 DTS(!!!!!!!!!!!!!!!) package
1 SSIS package.

And that was just to get data INTO SQL.  Also needed to rewrite to get the data back out to the client after we did our job with it.  Oh, and no way to get the historical data loaded.

This could ALL have been prevented if the original developers had just loaded all address information available in the file from day 1 (all lines have been in the file since the very first test file we got, which I managed to dig up).  Instead it was a major undertaking.


So, please, take a moment to think how the system will be used, and how it could evolve.  It can save major headaches in the future.