Re: SQL for Modelling Generalization Hierarchy
Date: Sat, 29 May 2004 11:46:10 +0200
Message-ID: <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.
> 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).
[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?
As I said: it is not just a problem,
but a problem pattern.
Received on Sat May 29 2004 - 11:46:10 CEST