Re: Stupid Database Tricks

From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Tue, 22 May 2007 17:48:09 -0300
Message-ID: <465356bd$0$4056$9a566e8b@news.aliant.net>

David Cressey wrote:

> "Cimode" <cimode_at_hotmail.com> wrote in message
> news: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?

> 
> I prefer the title "Stupid Database Tricks".  This title is derived from
> David Letterman's "Stupid Pet Tricks".  I hope you can see behind the
> levity,  and catch some real serious points here.  Sometimes, humor is the
> only sane response to absurdity.
> 
> A few sample stupid database tricks:
> 
> We back up our database every day.  But, come to think of it,  we have never
> actually tested our backups.
> 
> We found that views run awfully slow,  so we forbid views at our shop.
> 
> We found that indexes slow things down,  so we don't allow more than one
> index on each table.
> 
> We found that indexes speed things up,  so we have an index on every column
> of every table!
> 
> Data normalization?  We don't have time for all that theoretical crap!
> We're working under a deadline here! Talk to us after the version 1 rollout
> is complete.
> 
> Using our model, you can accomodate any new data that comes along with zero
> changes to the metadata.  That way, the DBA can't act as an obstacle to
> progress!
> 
> Our problem is a really simple one,  so we store everything in one giant
> table!
> 
> You get the idea...

Every table will have a numeric id column, and this column will be the primary key.

For performance, we enforce integrity in the application.

Duplicate removal is too expensive so we allow duplicates.

We back up our database every day -- reusing the same media every day.

We don't make backups because we our database handles recovery.

Joins are too expensive so we denormalized our data where we no longer need to do any joins.

Integrity enforcement is too expensive so we don't do any. Received on Tue May 22 2007 - 15:48:09 CDT