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:40bb719b$0$48933$e4fe514c_at_news.xs4all.nl...
> x wrote:
> > mAsterdam wrote:
> >>x wrote:
> >>>mAsterdam wrote:
> >>>>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.
> >>
> >>It won't bite you hard if you are absolutely
> >>sure that the generic employee can never have
> >>more than one role. But its redundancy will
> >>bite you.
> >>To see how, let's navigate some with this field:
> >>
> >>You look at employee.discriminatorfield to determine
> >>which subtype table (out of doctor, janitor)
> >>you are going to look for the subtype values? How?
> >>An extra table to associate values of the discriminator
> >>field with table names? Which extra constraints are needed?
> >>
> >>Now let's navigate without it.
> >>You take employee.key=K1 and see if janitor.key=K1 exists.
> >>if not you see if doctor.key=K1 exists.
> >
> > You can define the primary key of the employee relation to include the
> > discriminator.

>

It wont break "the key, the whole key and nothing but the key" the way you think it.
There is no employee.key without the discriminator. Think of employee.key as a disjoint union of the doctor and janitor primary keys.

> > (If you don't have "user" (DBA) defined data types)
> > Then you define the referential integrity constraints as usual.

>


> >>>Warning! I'm not an expert! (I'm too young for that :-)

> >>>

> >>>Do you have a good solution to this problem ?

> >>

> >>I've seen several solutions to similar problems

> >>(mostly invloving a table per subtype, not the scarce

> >>solution from the OP), but I never

> >>treated it in the generic sense it deserves.

> >>It would be a good candidate for a database design pattern

> >>if there would be such a catalog.

> >>In other words: it is not just a problem,

> >>but a recurring type of problem (a.k.a. a problem pattern).


> >

>

>

I don't know. I don't know other data models. :-)

> [snip]
> >>The people involved? What do you mean: The doctors,
> >>janitors and other employees or the database designers?
> >
> > The database designers. :-)
> >
> >>As I said: it is not just a problem,
> >>but a problem pattern.
> >
> > It is a problem when a problem pattern does not have a good solution.

>

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

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