Re: Modelling Disjoint Subtypes

On Mar 22, 7:18 pm, Joe Thurbon <use..._at_thurbon.com> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -

If I may dive down to the level of a a specific implementation for a moment... I recently ran into the need to represent disjoint subtypes for the first time (!) and ended up using a solution along these lines. The DBMS is SQL Server 2005. I found it disappointing that I wasn't able to define "computed" columns on the subtype tables to serve as the type specifier. They instead needed to be regular columns with a column constraint to only allow the appropriate type value.

But I suppose I shouldn't have been suprised -- this is the DBMS that doesn't support declaring foreign keys "to" or "from" views. How much easier things would be if it did!

TroyK Received on Fri Mar 23 2007 - 16:36:28 CET