# Re: Modelling Disjoint Subtypes

Date: Sun, 25 Mar 2007 11:26:10 GMT

Message-ID: <m%sNh.1586$M.519_at_news-server.bigpond.net.au>

Marshall wrote:

> On Mar 22, 4:19 pm, Bob Badour <bbad..._at_pei.sympatico.ca> wrote:

>> And if we had true physical independence, how much storage might the >> constant column occupy on disk?

*>*

> Yeah, we've mentioned this in the past.

*>*

*>*

*> type T = {subtype1, subtype2}*

*>*

*> P(id, t:T, ...) -- Parent table has id, t of type T, and other*

*> columns*

*> S1(id, t:T, ...) -- Subtype1 table has id, t of type T, and other*

*> columns*

*> (S2 omitted)*

*>*

*> Constraints on P*

*> primary key(id, t)*

*>*

*> Constraints on S1*

*> constraint c1: primary key(id, t)*

*> constraint c2: t = subtype1*

*> constraint c3: foreign key(id, t) references P(id, t)*

*>*

*> By c2, the t value is always subtype1, so we could instead*

*> declare S1 like this:*

*>*

*> type T1 = {subtype1} -- note that T1 is a subset of T.*

*>*

*> S1(id, t:T1, ...)*

*>*

*> and the foreign key remains the same, and does the same thing.*

*>*

*> constraint c3: foreign key(id, t) references P(id, t)*

*>*

*> Having declared to the system that the type of S1.t is*

*> a unit type (meaning the type has exactly one value)*

*> the system is free not to store anything for the t column.*

Hi Marshall,

To declare the type of the column, you'll need some storage. That is, I think what your suggesting moves some of the space required 'out of the column values' and 'into the column definition'. If reckon that to store one of N types, you'll need at least log2(N) bits.

> This is possible because the number of bits needed to

*> represent a value of a given type is the log2 of the number
**> of possible values. Since T1 has 1 value, the number
**> of bits needed is log2(1) = 0.
*

But there are now N extra types, and you'll need to store them somewhere.

*>
*

> So the system can use 0 bits to store values of

*> type T1. (Of course, it will need zero bits for each
**> row, so we have to multiply the 0 by the number of
**> rows to determine the total cost for the relation.)
**>
*

:)

[...]

Cheers,

Joe
Received on Sun Mar 25 2007 - 13:26:10 CEST