Re: are sql shortcuts a good practice ?
From: Misha Dorman <misha_at_no_mishapen_spam.co.uk>
Date: Thu, 21 Jul 2005 22:42:00 +0100
Message-ID: <11e05fahcmncj17_at_corp.supernews.com>
>>A,B,C,D are tables linked together with foreign keys :
>>A->B->C->D
>>What the former team has done is to store the value of the foreign key A
>>in D. This is what I call a shortcut : instead of having to jump through
>>the diffents links, you just retrieve the last value (D).
>>[...]
>>is it good or bad ?
> This is bad. Bad bad bad bad bad.
Date: Thu, 21 Jul 2005 22:42:00 +0100
Message-ID: <11e05fahcmncj17_at_corp.supernews.com>
Marshall Spight wrote:
> gabriel wrote:
>>A,B,C,D are tables linked together with foreign keys :
>>A->B->C->D
>>What the former team has done is to store the value of the foreign key A
>>in D. This is what I call a shortcut : instead of having to jump through
>>the diffents links, you just retrieve the last value (D).
>>[...]
>>is it good or bad ?
> This is bad. Bad bad bad bad bad.
Definitely, since it normally results in D being unnormalised, or in additional consistency constraints that need to be enforced somehow (e.g. that D.A_ID = A.A_ID from the join via C and B).
Only likely to be a good idea if A_ID forms part of a candidate keys of both D and C (i.e. when it is not a BCNF violation in D and the consistency constraint can be enforced via PK/FK DRI).
Misha Received on Thu Jul 21 2005 - 23:42:00 CEST