Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Bad Database Design

By Andy Warren,

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!

Total article views: 859 | Views in the last 30 days: 2
 
Related Articles
FORUM

Database design

Very Large database design

FORUM

Designing Reporting Platform Database

SQL 2000/2005 reporting design

FORUM

Database design issue

Database design

FORUM

Database Design Question

Database Design Question

FORUM

Database design

Database design question

Tags
database design    
editorial    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones