What's this join technique called ?

From: Andy Dingley <dingbat_at_codesmiths.com>
Date: Thu, 20 Oct 2005 14:11:02 +0100
Message-ID: <j65fl1hvpj77g2p1qds2om5tlklv94d1p6_at_4ax.com>

I have a CMS (web content management system). It's based on MS SQL, with a stored-procedure query layer and XSLT for the presentation. Articles are in an "article" table. Site structure is a hierarchy of Site / Section / SubSection / SubSubSection, a simple tree of tables with keys of section-id, subsection-id etc.

Articles can be "homed" to one or several locations simultaneously. They appear on the site wherever they're the latest article(s) for that location, either section, subsection or whatever.

There's a single table to implement this many-to-many link (which is perfectly standard SQL). On one side of the join it's a simple foreign key on article-id. However on the other it's a compound key of intra-table-id and level. "level" identifies whether it's a section or sub-section and intra-table-id is a foreign key onto _either_ the section-id, subsection-id etc. according to the value of level.

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" ?

-- 
 Cats have nine lives, which is why they rarely post to Usenet. 

Received on Thu Oct 20 2005 - 15:11:02 CEST