Re: Modelling Disjoint Subtypes
Date: Fri, 23 Mar 2007 01:18:40 GMT
Message-ID: <QVFMh.593$M.497_at_news-server.bigpond.net.au>
Bob Badour wrote:
> Joe Thurbon wrote: >
>> 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).
>>>
>>>
>>> The foreign key constraints for N-1 subtypes require N-1 foreign key
>>> constraints.
>>>
>>> If one uses foreign key constraints to enforce disjointedness among
>>> the same subtypes, presumably one may use at least as many foreign
>>> key constraints.
>>>
>>
>> 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.
>>>
>>>
>>> Remember: K is a set of attributes.
>>>
>>
>> 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)
>>
>> where, SubOne(ID, Type) has to reference a Super(ID, Type) and thus no
>> SubTwo could reference the same ID because the Type would be incorrect?
>>
>> Hmm, that seems more elegant!
>>
>> But is it what you were intending?
> > It's what I would do. >
It's a nice solution, I think.
Thanks very much for taking the time to lead me through it. It was (eventually) very satisfying.
>
>> 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.
> > And if we had true physical independence, how much storage might the > constant column occupy on disk?
If you had N subtypes then you need ~ log2(N) bits to store any of the values for the 'constant'. With E entities that are being modelled, you'd need to store
(1) a value SUPER.TYPE once for each entity: E*log2(N) bits, (2) a value SUBTYPE_X.TYPE once for each X : N*log2(N) bits.
So for one SUBTYPE table, you need about log2(N) bits to store the constant, which is what I think you were driving at. And if you gave the system some hints, you might even be able to get away without storing the SUPER.Type values (1) explicitly, since it will be derivable from the incoming reference (but it would have to be a pretty big hint unless you want to start changing physical representation at run-time).
Cheers,
Joe
Received on Fri Mar 23 2007 - 02:18:40 CET