I think most of us that work with SQL Server have our share of bad database design stories. I’ve learned to be more tolerant over the years as I’ve managed both the database and developer sides of the effort, but even tolerance only extends so far!
Some time back I worked with a client that had purchased a third party application and paid for extensive customizations to it, something we’ve all seen, but this one came with a twist. Because the product was designed to run on just about any database platform, everything was built for the lowest common (or worst) denominator. In this case all the data access was done using an API cursor, severely limiting our ability to tweak things that needed tweaking.
If that was the extent of the problem it would be an average example of bad design, but they went the extra mile. Because some platforms are case sensitive, any column that you wanted to search on had to have a second, all uppercase version of the column. So if you had a column called city that might contain ‘Orlando’, you also had to have a column named city__ that contained ‘ORLANDO’. No triggers existed to maintain them, so you could easily get out of sync if someone edited data directly, and of course, the rows are wider, increasing space usage and data access costs.
But wait, there’s more!
Because not every platform supported the equivalent of an identity or a uniqueidentifier, they created their own key generation scheme – a base 36 key, which in our system was derived by inserting into an identity column, selecting back the value and then transforming it to base 36, resulting in a 12 character primary key.
But wait, there’s more!
In addition, the first three characters of the key had additional meaning – just what you want in a good primary key. But the best part, and perhaps the most amusing, is that the keys were frequently sent back to clients to code mailings with – right on the mailing label. Because the key was comprised of 26 upper case letters and the digits 0-9, it was entirely possible for keys to be generated that were words, and some of them, ahem, not nice words!
