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:40bc3792$0$34762$e4fe514c_at_news.xs4all.nl...
> x wrote:
> > mAsterdam wrote:
> >>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 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.
> >>
> >>Well, yes you could.
> >>You are well aware that this is a relational no-no,
> >>let's make it explicit anyway. The normal form
> >>defined by "all non-key attributes should be dependent
> >>on the key, the whole key and nothing but the key" (BCNF)
> >>would be violated by this inclusion, because an employee
> >>is identified by just employee.key without the discriminator.
> >>But in SQL you could, and I doubt if any other language in
> >>existence would prevent it.
> >
> > 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.

>

No.
But this does not mean that I promote this kind of design. I only brought it to your attention.

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

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