Home -> Usenet -> comp.databases.theory -> Little design mistakes that can easily be avoided (1): Concatenated keys and addition of columns

Little design mistakes that can easily be avoided (1): Concatenated keys and addition of columns

From: Cimode <cimode_at_hotmail.com>
Date: 22 May 2007 06:26:01 -0700
Message-ID: <1179840361.196065.235210_at_n15g2000prd.googlegroups.com> 





Hi,



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





It came to my attention lately that several developers make a strong
confusion onto how unicity should be implemented. (at least the one
that know that a concatenated keys exists!)...These developers usually
implement unicity by making a sum between the values in the two
columns and implementing unicity on that sum.



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 - 15:26:01 CEST












Original text of this message