are sql shortcuts a good practice ?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

gabriel - 07 Jul 2005 11:26 GMT

greetings,

I'm taking over an old DB and cannot decide if something which was done
in it is good or bad :

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).

Of course, this means that you have to make sure that when changing
values, you change it at both places which can be risky.

As a developper, I find it rather convenient, of course, but the
question that keeps gnawing at me is this :
is it good or bad ?
What if I had 10 jumps instead of 4, would it be still bad ?

Come to think of it, I guess views should have been used instead.

This is not a troll, I'm no DB designer, I'm just a java developper
trying to figure out the way the db works :)

thank you for your opinions...

Reply to this Message

Marshall Spight - 15 Jul 2005 06:06 GMT

> greetings,
>
[quoted text clipped - 11 lines]
> question that keeps gnawing at me is this :
> is it good or bad ?

Wow. I just noticed this post. I'm amazed no one responded.
So I'll respond:

This is bad. Bad bad bad bad bad.

> What if I had 10 jumps instead of 4, would it be still bad ?

Still bad. The badness doesn't go down any as the number of
tables goes up.

The reason is exactly what you described: you have information
in two places, and when you update it, you have to update two
places. You also now have to figure out what do you want to do
when they differ. Usually if they differ it means something
somewhere screwed up, and there's no way to tell what it was
or what the right thing to do is.

I have seen *exactly* this sort of "feature" cost companies
big money. Like more money than the annual salaries of the
team of brainiacs who came up with this shortcut.

Good schema design requires a place for everything and
everything in its place. If something has two places,
that's not good.

Marshall

Reply to this Message

Misha Dorman - 21 Jul 2005 22:42 GMT

Marshall Spight wrote:

>>A,B,C,D are tables linked together with foreign keys :
>>A->B->C->D
[quoted text clipped - 4 lines]
>>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

Reply to this Message