Re: SQL for Modelling Generalization Hierarchy

"x" <x-false_at_yahoo.com> wrote in message news:40b72d9e_at_post.usenet.com...
> **** 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
?

yes, yes, no.
--dawn Received on Fri May 28 2004 - 20:01:07 CEST