Re: Examples of SQL anomalies?

"Roy Hann" <specially_at_processed.almost.meat> wrote in message news:U9qdnWeUwPYBmuvVnZ2dnUVZ8qfinZ2d_at_pipex.net...
> David Cressey wrote:
>
>
> > Here's the best I can do with OWA/CWA as it applies to NULLS. Treat
every
> > table with NULLABLE columns as a materialized outer join between a
table
> > containing the PK of that table and the nullable column, and some other
> > table with the rest of the data in it. (For purposes of this
discussion,
> > I'd like to consider a column "non nullable" in the situation where the
> > schema does not forbid NULLS but every application that inserts or
updates
> > data is guaranteed to weed out NULLS. This isn't necessarily a good
> > practice.)
> > Let's call the decomposed tables the "normalized equivalent" of the
table
> > with a NULLABLE column.
> >
> > It can be seen fairly easily that the omission of a row in the {PK,
Optional
> > Data} table corresponds to the inclusion of a NULL in the optional data
> > column of the composed table.
> >
> > Now the question of "What does the NULL mean" devolves down to the
question
> > of what the omission of a row means in the equivalent normlaized tables.
> > This is where OWA/CWA comes in. Under CWA, the omission of a given row
is
> > tantamount to denying the existence of such a row. The NULL must
therefore
> > mean "not applicable" under a CWA assumption.
> > Under an OWA assumption, the NULL could mean, "value unknown".
> >
> > Does this make sense?
>
> To some extent it does, but I think this entire discussion is utterly
> confused about something more fundamental than open- or closed-world
> hypotheses.
>
> The database is not reality. It is (only) a faithful record of our
> testimony *about* reality.
>

You are right. The divergence between reality and what the database says about reality covers a lot more ground than this discussion covers.

> A database design that presumes to model reality instead of modeling
> what we will actually say about reality is not fit for purpose. And if
> a model of the latter is more complicated, tough. Simple but wrong is
> not right.

In my practice, what NULL meant, pretty much all the time, was roughly this:

"In an ideal world, this NULL would not be here. Either the cell that contains it would be missing, or the cell that contains it would contain a value instead of a NULL. This isn't an ideal world. Let the reader deal with it."

Many times, I dealt with schemas that I did not design, and applications I did not write. In those situations, NULL might have meant whatever those in control wanted it to mean.
>
> --
> Roy
>
>
Received on Thu Jul 10 2008 - 16:26:09 CEST