Re: What's this join technique called ?

"Andy Dingley" <dingbat_at_codesmiths.com> wrote in message news:j65fl1hvpj77g2p1qds2om5tlklv94d1p6_at_4ax.com... [snip]

> This works, but obviously this "foreign key switching data-dependently
> onto diferent tables' primary keys" is a less than optimal situation for
> SQL structuring and optimisation. Many queries begin by flattening the
> site structure into a single list by unions across section and
> subsection.

>
> As far as I can remember my definitions this isn't non-normalisation as
> such, it's something more obscure. It's certainly not ideal and
> performance is a nightmare at the best of times. Does it have a
> specific name though, other than "generalised evil" ?
>
>
Before you can even begin to ask questions about normalization, you need to see whether your columns are or are not well designed. One trap that many designers fall into is to store multiple types of data into a single column, then use an adjacent column to disambiguate. It turns out that this is usually unfortunate.

I think, from reading your post that you are in this trap. Another instance of the same trap is the "One True Lookup Table" which as been beaten to death in this newsgroup in times gone by.

Try treating foreign keys to different tables as different "types of data", and storing them in different columns. See if queries don't work out easier. NULL values should weed out irrelevant joins.

Another thing to look into is how to design a generalization-specialization hierarchy. A few google seraches should get you some good online tutorials. This is a common problem in SQL, and learning the technique may help you evaluate your own situation. Received on Thu Oct 20 2005 - 15:32:27 CEST