Re: are sql shortcuts a good practice ?

gabriel wrote:
> 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).
> [...]
> 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 ?

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 Received on Fri Jul 15 2005 - 07:06:00 CEST