Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: SQL for Modeling Generalization Hierarchies

Re: SQL for Modeling Generalization Hierarchies

From: Erland Sommarskog <sommar_at_algonet.se>
Date: Thu, 27 May 2004 22:00:06 +0000 (UTC)
Message-ID: <Xns94F6F3E604E1Yazorman@127.0.0.1>


Robert Brown (robertbrown1971_at_yahoo.com) writes:
> The only solution I found for this is a generalization hiearchy where
> you have the employee table with all generic attributes and then you
> add tables for each new employee subtype as necessary. The subtype
> tables share the primary key of the employee table. The employee table
> has a "discriminator" field that allows you to figure out which
> subtype table to load for a particular entity.
>
> This solution does not seem to scale since for each value of
> "discriminator" I need to perform a join with a different table. What
> if I need to retrieve 1,000 employees at once?

So what? 1000 rows for a modern RDBMS is a breeze.  

> Is that possible to obtain a single ResultSet with one SQL statement
> SQL?
Yes, although with many discriminators, it will be one hell of a join:

   SELECT main.col1, main.col2, ... d1.col1, d1.col2, ...    FROM main
   LEFT JOIN discriminator d1 ON d1.keycol = main.keycol    LEFT JOIN discriminator d2 ON d2.keycol = main.keycol    ...

In practice, things might be even messier, because some values might apply to four discriminators, but be irrelevant to the rest. In they the would be four different columns. Although, this could be addressed with:

    common_to_four = colaesce(d3.common, d6.common, d9.common, d11.common)

But it may be better to add this as a nullable column to the common table.

Likewise, if two discrimiators are very similar, it may be better to lump them in the same table.  

> I guess another approach would be to use name/value pairs but that
> would make reporting really ugly.

And you get less control over your spelling errors. But sometimes this is the way to go.  

> Seems like a very common problem. Any ideas? Is this a fundamental
> limitation of SQL?

Sort of. SQL tables are squared, and object-oriented hierachies are jagged.

But that's alright, just don't be too object-oriented. Be pragmatic too.

-- 
Erland Sommarskog, SQL Server MVP, sommar_at_algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
Received on Thu May 27 2004 - 17:00:06 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US