« Some Updates | Main | Partial Pages in ASP »

An amusing story about databases and pants

I've found that my current career as an IT support guy has been rather rough on my clothes.  Crawling under desks, hauling computers and servers around, taking the old junk to storage, it's a lot for your typical polo and slacks to stand up to.  So a few years ago I switched to worker brands.  Carharts, Dickies, that sort of thing.  These last far longer, and they're passably business appropriate.  Well a pair recently started giving out, so I decided to give Duluth Trading company a try.  I make my order, and start eagerly awaiting delivery.

I tend to check delivery status every couple days for everything.  I don't know why, it's not like I can really do anything if there's a problem, and the crap I order is rarely important.  But I do, and the first few times I checked these everything was fine.  Then I check again and the status shows as "Exception", which I've never seen before.  It says to check the Shipment log for more details, so I scroll down and find this:

TRAIN DERAILMENT?!  That was certainly a new one for me.  Apparently this sort of thing isn't entirely unprecedented for poor Helena.  I checked the status again today, and it's now stuck in Kalispell, MT due to severe weather conditions.  Now there's certain evidence that suggests UPS might be more into logistics than I am, but I feel safe in suggesting that Montana during the winter may not be the best route.  It's anybody's guess when these pants will actually arrive now, but I hear they're pretty tough, so I'm not too worried.

So what does that have to do with databases?  Glad you asked!  As I've started to get more serious about programming, I've had databases on my mind more recently.  One of the first things you're likely to learn about database design is that you want to avoid duplicating data in a table wherever it's feasible to do so.  If you're tracking something like, say, delivery statuses, you don't want to type the same delivery status messages in for the various records.  It's more efficient to make a separate table (sometimes called a lookup table) where you enter the status once, then reference the ID number of that status message in the original table.  This is more efficient in terms of data storage, and it makes it easy to change the message later if that's ever necessary.  Changing the description in the lookup table effectively changes it everywhere because the other places are just saving the reference to that description, not the description itself.

While this makes logical sense, I found it to be extremely counter-intuitive when I was starting out.  The natural approach for me was to try to make the table look like a spreadsheet, where everything I wanted or needed was in a single table.  So there would be a status column with the statuses written in.  Fine for a small table, but what if you need to change the wording of the status?  What if you made a typo in some of them?  It's just too messy and inefficient.  Storing the reference ID makes the table harder to read in the raw form, but how often are you really going to do that?

So to tie this all together, that delivery status field is the perfect candidate for a lookup table, and as I was looking at it that first time, slightly in awe, I wondered if that was a recent addition, or if train derailment came up in a planning session or requirements meeting.  Now I'm not up on train derailment statistics, but it seems like a definite possibility that it hasn't happened since UPS started this online tracking system.  Looking a little closer, I noticed there's a period at the end of derailment, but not at the end of any of the other entries.  That makes me think it was added later.  And I belive my suspicion was confirmed when I checked later and saw a slightly more informative description in its place.  Pretty weird, huh?

PrintView Printer Friendly Version

EmailEmail Article to Friend

Reader Comments

There are no comments for this journal entry. To create a new comment, use the form below.

PostPost a New Comment

Enter your information below to add a new comment.

My response is on my own website »
Author Email (optional):
Author URL (optional):
All HTML will be escaped. Hyperlinks will be created for URLs automatically.