Little design mistakes that can be easily avoided (1): Concatenated Keys and Addition of Attributes

One of the most frequent misconception I face when working is about how concatenation became a synonym for addition of attributes. Lately, I have had the following example in a board...

COL1       COL2
ABC        DEF
AB          CDEF

They end up with a statement such as (assuming we want to insert 'AB' in COL1 and 'CDEF' in COL2)

if not exists(select COL1, COL2 from sometable where COL1+COL2 = 'ABCDEF')
begin
INSERT....
end
else
begin
REJECT
end

But looking at the example what should be done is rather...

if not exists(select COL1, COL2 from sometable where COL1= 'ABC' AND COL2='DEF')
begin
INSERT....
end
else
begin
REJECT
end

I thought it would be useful to bring that to the attention of this NG readers equivalent misconceptions...What about numbering them and posting them from time to time...My guess is that a list on *little mistakes that can be easily avoided* theme could within a year constitute a nice guideline. Any thougths? Received on Tue May 22 2007 - 18:01:34 CEST