An Example of Bad Design


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.

  • I’ve been reading along for a while now. I just wanted to drop you a comment to say keep up the good work.

  • pete

    i read this post out of fear that a database i might have created a while back would be the example!

  • I’m currently working on a system like that also… for some unknown reason they have the number padded out to 13 characters, but the biggest value they have in there is 8932.

    LTRIM is certainly not a good option because of the table scan… a better approach would be…

    WHERE CO_NUM=STR(@IntegerToFind,10)

    …if you have an actual integer to look for, or…

    WHERE CO_NUM=RIGHT(SPACE(10)+@StringToFind,10)

    …if you have it in a string format (like ‘8644’).

    Anyway, I understand your pain. 😉