Re: SQL for Modelling Generalization Hierarchy

From: mAsterdam <mAsterdam_at_vrijdag.org>
Date: Mon, 31 May 2004 19:55:47 +0200
Message-ID: <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.

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.

> (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.

Of just the relational model? Which other model does *not* have this weakness? I have seen this problem surface in CODASYL databases when there wasn't yet a DBMS that claimed to be relational.

Employees and roles (Types and subtypes?) present some problems to any HR-administration design, not just the ones that happen to use an RDBMS.

[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.

Does somebody know if there is a database design pattern catalogue? Googling this gets me loads of java and XML. Received on Mon May 31 2004 - 19:55:47 CEST