Re: SQL for Modelling Generalization Hierarchy

• Post for FREE via your newsreader at post.usenet.com ****

"mAsterdam" <mAsterdam_at_vrijdag.org> wrote in message news:40b631f1$1$561$e4fe514c_at_news.xs4all.nl...
> Robert Brown wrote:
> [snip]
> > 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 "discriminator" field would also prevent an employee from
> having two roles at once. It would not be possible to have
> an employee being a doctor and a manager at the same time.
> This may sometimes be want you want. Most of the time it won't.
> Even if it *is* what you want you can use constraints.
> You don't need the field. The "discriminator" field
> approach suggests to me that you look at the problem
> from a strictly navigational angle. Try other approaches.
> For instance try looking at it from an existential
> point of view.

"To exist is to be efficient"

This is a reporting problem. You need navigation in some circumstances. The Relational Model has not eradicated the navigation. The "discriminator" field might be just what is needed for defining the constraint.
Warning! I'm not an expert! (I'm too young for that :-)

Do you have a good solution to this problem ?

> [snip]
> > Is there a better approach to modelling these kind of heterogeneous
> > entities that does not involve creating a table for each new employee
> > type or having sparce tables (mostly filled with NULLS)
>
> What don't you like about tables for each type?
> > I guess another approach would be to use name/value pairs but that
> > would make reporting really ugly.
> >
> > Seems like a very common problem. Any ideas? Is this a fundamental
> > limitation of SQL?
>
> Fundamental? Dunno. I've seen it many times, not only in SQL.
> I suspect it is inherent to administration.

Do you solved the problem ?
If it is so frequent, maybe there is some standard solution to it. It would be interesting to see why the solution is not obvious. Is it the fault of the Relational Model, of SQL or of the people involved ?

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

• Usenet.com - The #1 Usenet Newsgroup Service on The Planet! *** http://www.usenet.com Unlimited Download - 19 Seperate Servers - 90,000 groups - Uncensored -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=