Stupid Database Tricks (was: Little Design Mistakes)

"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... Received on Tue May 22 2007 - 16:47:33 CEST