Re: SQL for Modelling Generalization Hierarchy
From: mAsterdam <mAsterdam_at_vrijdag.org>
Date: Tue, 01 Jun 2004 10:00:26 +0200
Message-ID: <40bc3792$0$34762$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 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.
Date: Tue, 01 Jun 2004 10:00:26 +0200
Message-ID: <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.
Sorry, I don't get it.
Would employee.key *excluding* the "discriminator field" uniquely
identify an employee?
>>>(If you don't have "user" (DBA) defined data types)
>>>Then you define the referential integrity constraints as usual.
[snip]
>>>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 Tue Jun 01 2004 - 10:00:26 CEST