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…