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.
SQL Server, SSIS, SSAS and maybe even SSRS rants and raves (and, on occasion, useful code or designs).