36 Chambers – The Legendary Journeys: Execution to the max!

February 20, 2014

T-SQL Anti-Patterns: Zombie Fields

Filed under: Database Administration — Kevin Feasel @ 10:05 pm

Today’s T-SQL anti-pattern is what I call zombie fields.  Like the SELECT * anti-pattern, this one shows laziness on the part of a developer—or, occasionally, a group of people working around a limitation in a piece of third-party software.

What I mean by zombie fields is re-using fields for some purpose other than their intention.  For example, the Address3 line is a common zombie field.  Very few people have a third address line, so in a number of contexts, it’s a “free” line that you can use to fill in an e-mail address or other piece of information.  There are two major problems with zombie fields.

The first major problem is that they are inherently counter-intuitive.  When you want to query e-mail addresses of your userbase, it simply does not make sense to look in Address3.  Furthermore, it’s usually not as cut-and-dried as a field replacement.  Rather, these situations tend to be more like, “Well, for warehouse distributors, we use Address3 to store their primary contact.  For retailers, we use Address3 to store our third-party distribution network.  For individual customers, we use Address3 to store their occupation…except for the people who use Address3 to store comments that they couldn’t fit in the Notes field.  Oh, and there was that time when we started using the first eight characters of Address3 to determine what shipping zone a client was in.  Not to mention those facilities which had a second telephone number, so sometimes we put the primary contact in the Notes field and the secondary telephone number in Address3.  And Gloria in AR used that field because she didn’t want to tab to the second page to put in her notes.”

The second major problem is that you lose one of the major benefits of a relational database:  constraints.  If I want to store e-mail addresses, I know I want a varchar (or nvarchar) field, that my e-mail address will be at least three characters with an @ somewhere after the first character but before the last.  If I want to store a phone number, there are rules around that, too.  Storing either in a long(ish) attribute with no checks makes it harder to keep bad data from entering your system.

The correct approach is to use attributes for their intended purposes.  If you need an e-mail address attribute, create one.  If you need a third-party distribution network key, add one.  If you need a second telephone number attribute, add one.  Those also involve updating whatever interface users work with, and that has a cost.  But that’s a cost of doing business; using zombie fields is a bad answer.  The only case it really makes sense is if you bought a product from a company which went out of business and can’t support the product any longer.  And in that case, it’s probably a good idea to start looking for a replacement…

About these ads

2 Comments »

  1. […] the previous T-SQL anti-pattern, I discussed zombie fields.  One of the reasons to avoid zombie fields (aside from the risk of infection) is that you […]

    Pingback by T-SQL Anti-Patterns: Unconstrained Data | 36 Chambers - The Legendary Journeys: Execution to the max! — February 21, 2014 @ 6:00 pm

  2. […] annoying DBA can’t complain about this; it’s normalized, you have good constraints, and your fields are exactly what they say.  In addition, you only need this one table for all item attributes.  Best of all, your job is […]

    Pingback by T-SQL Anti-Patterns: EAV | 36 Chambers - The Legendary Journeys: Execution to the max! — February 23, 2014 @ 6:02 pm


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Silver is the New Black Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 98 other followers

%d bloggers like this: