Re: Modelling Disjoint Subtypes

Bob Badour wrote:
> Joe Thurbon wrote:
>

>> Bob Badour wrote:
>>
>>> Joe Thurbon wrote:
>>>
>>>> Am I just barking up the wrong tree? Would you like to tell me the 
>>>> answer?
>>>
>>> You are barking up the wrong tree. If at all possible, the only 
>>> constraints one should have are foreign key references. What exactly 
>>> prevents one from using a foreign key constraint to enforce the 
>>> disjoint requirement?
>>
>> A lack of imagination on my part?
>>
>> More seriously, I think the answer is that a foreign key constraint 
>> enforces a constraint between columns in two tables. The disjoint 
>> entity requirement requires a constraint to be enforced between 
>> columns in N tables (where there are N-1 subtypes).

Fair enough.

>

>> Or, more explicitly, if
>> - a table COMMON contains columns that are common to a number of 
>> entities, and COMMON has a primary key K; and
>> - tables SUBTYPE1 ... SUBTYPEN represent specialisations of COMMON, 
>> these tables also have a primary key K which references K as a foreign 
>> key.

Do you mean something like this (for two sub-types):

Domain of ID is integer
Domain of Type is {SubOneType, SubTwoType}

Super

	ID    (primary key)
	Type  (may be any of SubOneType, SubTwoType)

SubOne
	ID   (primary key, references Super.ID)
	Type (must be SubOneType, references Super.Type)

SubTwo
	ID   (primary key, references Super.ID)
         Type (must be SubTwoType, referenecs Super.Type)

Hmm, that seems more elegant!

But is it what you were intending?

This also seems to be a situation where the often recommended practice of combining composite keys into a surrogate simple key would not work, since you're relying on the elements of the composite key to do some constraint enforcement.

Interesting.

Cheers,
Joe Received on Fri Mar 23 2007 - 00:56:49 CET