Re: Stupid Database Tricks
Date: 23 May 2007 08:20:46 -0700
Message-ID: <1179933646.729385.136570_at_m36g2000hse.googlegroups.com>
On May 23, 4:57 pm, "Roy Hann" <specia..._at_processed.almost.meat> wrote:
> "Brian Selzer" <b..._at_selzer-software.com> wrote in message [Snipped]
> I do notice that we are probably arguing at cross-purposes here. Marshall > was objecting to designs that *always* automatically include system > generated values; you are saying they *sometimes* have their uses. I > actually agree with that. > > Roy
I have to agree with this. Marshall's comment is a crystal clear warning against the unfounded/unjustified urge of calling auto incremental id's that are anything but primary keys. The reason is simple: As direct image systems have poor support of concatenated key's implementation, developpers end up creating meaningless pointers without implementing systematically the 1:1 cardinality between the surrogate key to be and the natural primary key...As a consequence, identity is basically left to randomness, not to mention that a bias is induced that sets become ordered to be processed...
It would be reasonnable to assume that a trdbms could or should infer at design time some surrogate key implementation solely based on the choice of a natural key. Such system couls be systematic if and only if, it actually implements the full set of requirements (among which the 1:1 cardinality forced between the natural key and the surrogate key) to allow it to become a true surrogate key implementation and not leave it as surrogate wanabe
I sometime come to the conclusion that surrogate keys should actually become internal to the dbms and out of the designer''s view. I look at it this way: either one implements *fully* the surrogate key requirements for being a true surrogate key, either not use it at all and favor a natural key (that a trdbms should support)
Just a matter of staying on topic, I add some interesting issues you are bringing.
CATEGORY 1: Little Design Mistakes that can be easily avoided
--> CONCATENATED KEYS VS ADDITION OF COLUMNS (CD, ML)
--> MISTAKING NUMERIC ID's (Surrogate key wannabe!) with PRIMARY KEYS
(BB, ML)
--> LETTING THE APPLICATION *HANDLE* THE BUSINESS LOGIC(BB)
--> AVOIDING SUBJECTIVITY INTO DESIGN(RH)
--> NOT IMPLEMENTING THE FULL SET OF CONTRAINTS WHEN IMPLEMENTING A
SURROGATE KEY(CD)
CATEGORY 2: Making your data administration life (under direct image
systems) less miserable
--> GETTING RID OF DATA REDUNDANCY (BB)
--> TESTING YOUR BACKUPS(DC)
--> USING VIEWS(DC)
CATEGORY 3: Straightforward fallacies (Could be called Snake Oils's
salesman argument)
--> DENORMALIZATION IMPROVES PERFORMANCE(DC)
--> BULLSHIT IS BEST PRACTICE (RH)
CATEGORY 4: Patterns of Behavior for Identifying the Ennemy and
Defeating him (How to kick snake oil's salesman B*TT)
--> ON HOW TO DEFEAT A SNAKE OIL'S SALESMAN ARGUMENT FOR KEYS(RH)
Received on Wed May 23 2007 - 17:20:46 CEST