RSS Feed

An Example of Bad Design

3

September 15, 2009 by Mike Hillwig

What’s a little extra padding? And I’m not talking about my waist line here. Today, I want to talk about one of my pet peeves of a current system. I know I keep beating up on this one table that’s really poorly named. It holds customer orders and the vendor has decided to call it co. Seriously. That’s not that the only pet peeve, though.

Here is a simple query on that table.

select co_num
from co

This looks pretty benign so far. Take note that 8644 exists in this table. Look what happens when I put 8644 in the WHERE clause. Now it’s in quotes because this is a NVARCHAR field.

select co_num
from co
where co_num = ‘8644’

Wait. Where did it go? We saw it when it wasn’t in the WHERE CLAUSE. I figured it must have somehow gotten a leading space.

select co_num
from co
where co_num = ‘ 8644’

Nope. That didn’t find it, either.  So I’m breaking out the big guns and using the LTRIM function to strip out the spaces.

select co_num
from co
where ltrim(co_num) = ‘8644’

Well, jeepers, it’s there after all. I hate having to use LTRIM in the WHERE clause because it forces a full table scan instead of using an index.

In a fit of frustration, I decided to see just how many spaces are being used to pad this column.

select len(co_num)
from co
where ltrim(co_num) = ‘8644’


Yes, that’s six spaces. Fortunately, this table isn’t huge. However, this maddening padding scheme propogates to all of the downstream tables. What’s worse, this vendor does the same thing with the customer, vendor, and po tables. (Yes, they abbreviate purchase orders down to PO.)

I can’t stress how bad of a design decision this is. While it’s not an issue with JOINs, any time you have to query based on one of these fields, it’s got to be doing a full table scan.


Search

Pages