SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

The best laid plans....

I had big plans for this blog, a place to rant, a place to put cool things I discovered, a place to post code I, and others, might find useful in future. Unfortunately, several months of 60-70 hour weeks have left me with little motivation to do much once I get home from work other than sleep.

Thankfully, this should be changing soon, and the anticipation of that happening has given me a little motivation back. So today, I'm going to have a short rant.

Up until about five years ago I was spoiled, but I didn't know it at the time. I was working (and had been for about 5 years) with a SQL Server 2000 database which acted as the back end for the core system for my employer. This database, a kind of ERP-lite, had been designed and built in house by the person who was now the IT manager. They knew their stuff, both as a programmer in a general sense and as a practitioner of relational design. We had keys, check constraints, defaults, consistent naming of columns and tables, and all those things that make a SQL database easy to work with and generally error free.

Then I moved to Canada for two years. While there I worked as a contractor and saw 5 or 6 more ERPs. I was shocked. I saw systems with virtually no foreign keys, absolutely no other database constraints. I saw schemas that used "ID", "company_number", "comp_code" and "company_id" as column names in diffferent tables, and even columns with different data types, for the same domain. I saw dates stored as integers. I saw stored procedures with cryptic names, following no apparent consistent standard, containing entirely cursor driven RBAR code.

I'm back in Australia now, and I'm sorry to say that these observations continue. The main system at my current company (developed by a small software vendor) also has virtually no foreign keys, no check constraints, and cursor driven code. It has disgusting, cryptic column names (seriously... "fld_current"?).

And I've found it's not limited to small shop vendor systems. TFS has a table with a column in it called "Not A Field". Spaces and all. I shit you not.

It seems database design doesn't get much respect, and I really don't understand why this is. The data in a company's main ERP system practically *is* the business. Database performance is one of the bottlenecks of most infrastructures. These days we see people struggling with master data management, complex data cleansing in their data warehouse ETLs, validation checks being handled "in the front end"... my experience from 5 years ago tells me that none of this is needed if you just build your database right.

I constantly try to communicate this idea to my employers, but the message never seems to gain any traction. I wonder why this is.

Don Halloran

SQL Server, SSIS, SSAS and maybe even SSRS rants and raves (and, on occasion, useful code or designs).


Posted by Anonymous on 17 September 2010

Pingback from  Twitter Trackbacks for                 SQL Server Central, The best laid plans.... - Don Halloran         [sqlservercentral.com]        on Topsy.com

Posted by Steve Jones on 20 September 2010

Good luck slowing down and hope to see some blogs coming soon.

I think the design issue is let go because so often we survive without it. Even without good design, even if it costs more later to fix, it isn't enough to get people to make the investment up front.

Posted by Malcolm Daughtree on 28 September 2010

If I may throw my hat into the ring.  Not only does this happen from company to company, it happens within the same department by the same bl****y developers where expediency overrules basic common sense.  I've seen the DBA world from both sides as a developer and a Production DBA (currently) and expediency and $$$ through the door are the norm not the exception.  I feel some days like I'm standing at the door with sword and shield and every one else is throwing hand grenades.  

Posted by Phil Helmer on 28 September 2010

People don't believe or don't concern themselves with what they don't see. I've seen this in several shops over my career.

The deliverable that needs to go out the door by the end of the week = visible. The extra maintenance work that is now needed because it had to go out the door last Friday = not so much.

The danger zone is when a few hack jobs ship and the customers, not being picky, are willing to overlook the flaws for the singing & dancing functionality that is there. Next thing you know, this becomes standard operating procedure and it takes a lot of organizational stress to right the ship. It happens much more quickly than anyone anticipates.

Until we adopt professional standards for working in our industry, we will always have to fight an uphill battle against the snake oil peddlers. Personally, I have taken to demonstrating the better practices to those around me in hopes that some of it sticks and they work it into their work the first time around.

Posted by call.copse on 29 September 2010

I know exactly what you mean and have been through those cycles several times. I am currently trying to lift standards at my current shop - it is not easy as to be overly critical is clearly not likely to be welcomed. 'WTF is that supposed to be?' when looking at someone's code tends to invite unpopularity so all you can do is try and show the way forward. You do have to establish credentials as an achiever before really having a go - fortunately not too hard if you actually have a clue.

Posted by twinsen on 29 September 2010

Can't complain here. Bad DB design and DB development is why I have my job now. :) Makes me look like the hero! Though I must admit, if I wasn't granted the power to change it I would definitely be singing a different song.

Posted by rporter 68345 on 29 September 2010

I've found that there is an increasing belief that a properly normalized structure with correct field types is considered bad design or at least unnecessarily complex and over-designed.

Posted by richardd on 29 September 2010

Dates stored as integers? You were lucky! Why, when I were a lad, we used to have to parse dates out of varchar(50) columns!

Seriously, dates stored as integers are a pain, but there are some systems which do need to store dates earlier than 1753, and prior to SQL 2008 there wasn't a built-in type to do it.

At one point I worked on a PoC project which needed to store BC dates, which even SQL 2008 can't manage. I ended up storing the value in a bigint field as an offset from year 1. It was a complete PITA, but it worked.

Posted by Anonymous on 29 September 2010

Pingback from  The Design Investment « Voice of the DBA

Posted by allmhuran on 11 December 2010

Steve and Malcolm: I think you've hit the nail on the head there. The issue seems to be that a lot of systems are built by systems analysts or developers with training in programming, but none in database design. The first person with the knowledge to critique the design or code is often the DBA, and by this point the developers and business are pushing to get the thing deployed to UAT and production. What's a DBA to do? I try to get involved earlier in the cycle, but I feel like that seagull who flies in, craps on everything, makes a lot of noise, and flies away. I personally don't have the time to hold people's hands through the entire development cycle of 10 concurrent projects.

Gregor: Hah, I suppose there's that. I'm not sure if it's worth it!

rporter: Yes, this worries me as well. It seems to stem from the increased takeup of ORM tools. My previous post was a rant about that :)

richardd: true, sometimes you're stuck. The system in question didn't need such dates.

Posted by allmhuran on 11 December 2010

Oh, and thanks for the feedback everyone!

Posted by Anonymous on 27 December 2010

Three posts, three critical rants. It's time to be constructive. I was given the responsibility to

Leave a Comment

Please register or log in to leave a comment.