Re: theoretical question on the RDBMS

Paul,

> I think perhaps (guess who's been reading the dbdebunk website) this
> is something that should be done at the physical, not logical level.
> i.e. behind the scenes the database could implement "sparse" columns
> in a separate "file" for performance reasons but at the logical (SQL)
> level it should be in the same table.
>

I'd suggest that there are both logical and physical implications to columns which may contain NULLS.

The physical implications have to do with sparseness, as you remarked. In addition to whatever space may be saved, there may be speed considerations when retrieving either or both tables, if they are separated into two tables.

There are also logical implications to storing the optional column in a separate table, along with the primary key. First, every truly optional column (one where NULLS might actually appear) is a departure from the strict relational model. In a strict relational model, the tables represent relations. And, in a relation, there are no NULLS.
Second, every truly optional column represents an outer join of some kind, and therefore a departure from full normalization.

The above may or may not be reasons to choose one logical design over another, but there are consequences, at the logical level, to either design choice. Received on Thu Aug 15 2002 - 20:45:36 CEST