Re: Modelling Disjoint Subtypes
From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Fri, 23 Mar 2007 00:19:51 GMT
Message-ID: <H2FMh.13919$PV3.143004_at_ursa-nb00s0.nbnet.nb.ca>
>
> Fair enough.
>
>
> 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?
Date: Fri, 23 Mar 2007 00:19:51 GMT
Message-ID: <H2FMh.13919$PV3.143004_at_ursa-nb00s0.nbnet.nb.ca>
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.
> 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? Received on Fri Mar 23 2007 - 01:19:51 CET