Re: teaching relational basics to people, questions

"Roy Hann" <specially_at_processed.almost.meat> wrote in message news:N8Sdnf8ODpfs8ZfWnZ2dnUVZ8g-dnZ2d_at_pipex.net...
> Mr. Scott wrote:
>
>>
>> "Roy Hann" <specially_at_processed.almost.meat> wrote in message
>> news:3eudnYx_I7CpiZXWnZ2dnUVZ7qSdnZ2d_at_pipex.net...
>>> Sampo Syreeni wrote:
>>>
>>>> [snip] And since
>>>> one would have to have a bona fide range datatype, building in
>>>> handling for infinite ranges would also be easy; that'd get rid of one
>>>> of the most persistent reasons why people incorporate nulls into
>>>> designs.
>>>
>>> I think you are being excessively optimistic. The most persistent (and
>>> most common) reason people incorporate nullable columns into designs is
>>> because they have a misplaced desire to minimize the number of tables in
>>> the design, and think that conflating multiple fact types in one table
>>> is clever, efficient, and harmless.
>>
>> I think you are oversimplifying. The most persistent (and most common)
>> reason people incorporate nullable columns into designs is the not so
>> misplaced desire to provide for information that is relevant but not
>> required, and that doesn't necessarily involve conflating multiple fact
>> types in one table. Each table design represents a family of
>> interdependent
>> predicates.
>
> That last sentence is the crux of your objection to my comment. I guess
> since you and I are implicitly talking about SQL and since SQL makes few
> if any claims to fidelity with the relational model, you are entitled
> to think whatever you like about tables that represent "a family of
> interdependent predicates". Personally I have no idea what those are
> supposed to behave like,

That sentence applies not only to SQL tables but also to Codd's time-varying relations and Date and Darwen's relvars. Date defines a relvar's predicate as the conjunction of all of the constraints that apply to it, but I think he's wrong. The logical connective should be IFF rather than AND. The difference is subtle, and may at first glance appear problematic since IFF is true whenever none of its operands are true, but under the closed world assumption, the only atomic formulas that are ever represented in the database are those that are supposed to be true, so there is no harm in choosing IFF over AND. The main reason I think the connective should be IFF rather than AND involves deletes. While inserting a row effectively asserts that all of the atomic formulas represented by the row are true, regardless of the whether the logical connective is IFF or AND, the same can't be said for deletes. When the logical connective is IFF, deleting a row effectively denies that any (not all) of the atomic formulas represented by the row are true, but when the logical connective is AND, deleting a row effectively denies that all (not any) of the atomic formulas represented by the row are true, which can be even if only one isn't. If some but not all of the atomic formulas represented by a row were true, but a row can't be in the table unless they're all true, then where is that positive information to be stored? Without anywhere to store it, the database is inconsistent.

> but I am pretty sure that in practice they
> must suck. Certainly every example I've ever had to deal with did. At
> this point I invoke the Principle of Incoherence and drop out of the
> discussion.
>
> I will however allow that you may be right that yours is an even more
> common reason people introduce nullable columns. It would be worth
> investigating which really is most common, so that appropriate remedial
> training can be devised.
>
> [much erudite-looking stuff snipped]
>
> --
> Roy
>
Received on Thu Nov 26 2009 - 11:19:14 CET