THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Allen White

Don't Do This

In working on applications at client sites you tend to come across code that makes you think "what were they thinking"? This week I found one that just made me do the "facepalm":

select top 1 @_StateTrueKey = OverEngineeredKey from OverEngineered
where Value = 'true'

select top 1 @_StateFalseKey = OverEngineeredKey from OverEngineered
where Value = 'false'

(The names have been obviously obfuscated.) When I saw this I had to look at the table contents:

OverEngineeredKey        Value
0              False
1              True

OK, it's really great that you want to be flexible in your design, and allow growth, but this is just plain silly.

Use constants in your code where you have truly constant values. Things like True/False, Male/Female, Off/On, etc. aren't going to "grow" and gain new values in time. Use constants in your code, and save the trips to the database for things that really will change over time.

Allen

Published Friday, June 25, 2010 12:14 PM by AllenMWhite

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Jen McCown said:

Hear, hear!!  That qualifies as a true code sin ("Coding Dumb", certainly), though I don't know what to actually call it...

June 25, 2010 11:33 AM
 

drsql said:

Well, you may get a few people noting that Male/Female is questionable :)  But seriously, true and false is obviously fine with a number, but what would be your suggestion for the the other ones?  

The main limitation with this is that the client has to know of the domain names in case that no values of one type exist. I often would create the table, use a simple text value for the key (Paul Nielsen has convinced me of this over time), and then optionally have a table for the domain to allow for additional information like sorting and such (or short or long versions of the value for reports).

I do love the Top 1 though, that is classy :)

June 25, 2010 11:33 AM
 

Keith Mescha said:

Seen this way to many times. In my experience this often happens when vendors use a code converter tool to port an app written for a different platform to make it SQL Server supported.

Have a ton of this at my shop. Frustrating for sure and hands tied quite a bit of the time.

June 25, 2010 11:36 AM
 

dmmaxwell said:

0 = False

1 = True

2 = Maybe

3 = idunno

4 = Four

5 = Ask your mother.

June 25, 2010 11:38 AM
 

Drew said:

I think it might be worth leaving room for expansion in case you ever need to  do this:

http://thedailywtf.com/Articles/What_Is_Truth_0x3f_.aspx

:-p

June 25, 2010 2:56 PM
 

James Luetkehoelter said:

Hey Allen, I want a quarter for "What were you thinking" :)

Yeah, see this a lot. I've never understood why people use a lookup table with values "Yes|No" or "True|False". Gender actually gets tricky. Having done some work with the federal government, some data systems have 9 (yes 9) different values for Gender :) And of course, they don't make sense...

June 26, 2010 2:22 PM
 

James Luetkehoelter said:

oh and dmmaxwell, I've that - I'm going to include that in every system I build from now on... :)

June 26, 2010 2:23 PM
 

jonmcrawford said:

I just assumed when I read this that it was for a law office, and therefore additional values were needed...

June 29, 2010 9:47 AM
 

GrumpyOldDBA said:

my favourite is nullable bit fields which I find very often .. but I guess it fits the true / false / dunno !

I sort of get to love columns such as IsActive in the client table, which may be true/false/null ! And yes I've found many of the same style of lookups for such similar in clients I vist - but REALLY worry when they use functions to return true or false - yes I have seen it and yes it brought the system down when implemented ( functions for constants in place of a lookup table )

June 30, 2010 8:33 AM

Leave a Comment

(required) 
(required) 
Submit

About AllenMWhite

Allen White is a consultant and mentor for Upsearch Technology Services in Northeast Ohio. He has worked as a Database Administrator, Architect and Developer for over 30 years, supporting both the Sybase and Microsoft SQL Server platforms over that period.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement