Re: SQL for Modelling Generalization Hierarchy
Date: Mon, 31 May 2004 20:28:17 +0300
Message-ID: <40bb6a4e_at_post.usenet.com>
- Post for FREE via your newsreader at post.usenet.com ****
"mAsterdam" <mAsterdam_at_vrijdag.org> wrote in message
news:40b85bde$0$559$e4fe514c_at_news.xs4all.nl...
> 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.
(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).
Yes. It is a problem (pattern) that surface the strenghts, weeknesses of the relational model and of its understanding.
> [snip]
> >>>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 ?
>
> RM at fault here? I don't think so - I'ld be interested to hear in which
> model this problem does not exist.
> SQL at fault here? No. SQL is problematic, but this is not SQL's
> problem. SQL may help you describe it.
> 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 -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=