Path: news.f.de.plusline.net!news-fra1.dfn.de!news-nue1.dfn.de!newsfeed.r-kom.de!newsfeed.freenet.de!club-internet.fr!feedme-small.clubint.net!proxad.net!feeder1-1.proxad.net!198.186.194.247.MISMATCH!news-out.readnews.com!news-xxxfer.readnews.com!postnews.google.com!x18g2000prd.googlegroups.com!not-for-mail
From: Cimode <cimode@hotmail.com>
Newsgroups: comp.databases.theory
Subject: Little design mistakes that can be easily avoided (1): Concatenated Keys and Addition of Attributes
Date: 22 May 2007 09:01:34 -0700
Organization: http://groups.google.com
Lines: 48
Message-ID: <1179840222.955526.131500@x18g2000prd.googlegroups.com>
NNTP-Posting-Host: 195.154.188.107
Mime-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
X-Trace: posting.google.com 1179849708 15613 127.0.0.1 (22 May 2007 16:01:48 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Tue, 22 May 2007 16:01:48 +0000 (UTC)
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.3) Gecko/20070309 Firefox/2.0.0.3,gzip(gfe),gzip(gfe)
X-HTTP-Via: 1.1 localhost:5656 (squid/2.5.STABLE11)
Complaints-To: groups-abuse@google.com
Injection-Info: x18g2000prd.googlegroups.com; posting-host=195.154.188.107;
   posting-account=XWbNBg0AAABXMdLVqoM3R9n-RIU90_cb
Xref: news.f.de.plusline.net comp.databases.theory:43987

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?