# Re: Modelling Disjoint Subtypes

From: Joe Thurbon <usenet_at_thurbon.com>
Date: Sun, 25 Mar 2007 11:26:10 GMT
Message-ID: <m%sNh.1586\$M.519_at_news-server.bigpond.net.au>

```>> 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

Original text of this message