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: Laconic2 <laconic2_at_comcast.net>
Date: Thu, 27 May 2004 13:27:25 -0400
Message-ID: <E8KdnXHBZNMsuSvdRVn-jg@comcast.com>

"Robert Brown" <robertbrown1971_at_yahoo.com> wrote in message news:240a4d09.0405270856.4ce55c7d_at_posting.google.com...
> Is there a good approach to modelling many heterogeneous entity types
> with that have some attributes in common?

This is a frequently asked question. Unfortunately, the frequently given responses don't usually settle the matter.

> There are many employee subtypes and more can be dynamically added
> after the application is deployed so it's obviously no good to keep
> adding attributes to the employees table because most attributes will
> be NULL (since janitors are never doctors at the same time).
>
> 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.

If you invent new subtypes on an adhoc basis, (e.g. a DBA is never either a janitor or a doctor)
and you invent new tables for subtypes on an equally ad hoc basis, I'd suggest that you are altering your data model on an ad hoc basis.

You can do that if you want, but don't expect the same power and simplicity that you get from a stable data model.

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

Why would you ever need the attributes of more than one subtype? If your query is about doctors, why would you need to join the table about janitors into the query?

If your query is about employees, why would you need to know any of the subtype attributes?

>
> Or do you I need to iterate look at the discriminator and then
> perform the appropriate join? If this kind of iteration is necessary
> then obviously this generalization hierarchy approach does not work in
> practice
> since it would be painfully slow.

You need to ask two questions about the model, and about any imlpementation based on the model:
first, is it correct and complete? Second, if yes to the first, is it fast enough?

In general, different implementations of the same model should be transparent to users of the imlpementation who only look at the features visible in the model.

>
> Is there a better approach to modelling these kind of heterogeneous
> entities with shared attributes that does not involve creating a table
> for each new employee type or having sparce tables (mostly filled with
> NULLS)
If you have new entities, you are going to have new relations. That either means having new tables, or fudging the relationship between tables and rleations. Do the second one at your own peril.

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

I would suggest it goes beyond SQl to the very heart of using foreign key/ primary key aossciations to establish linkages. Received on Thu May 27 2004 - 12:27:25 CDT

Original text of this message

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