At 9:44 AM +1100 12/14/07, Ron Savage wrote:
>It is a good idea to declare all your base tables with NOT NULL
>constraints on all columns whenever possible.
>Maybe that's pointing you to the real problem.

At 8:07 AM +0100 12/14/07, Michael Lackhoff wrote:
>But I wouldn't count this as a bug. After all there is no way for a
>query parameter to keep the difference between undef/NULL and ''.
>Both would look like http://myserver/myapp?anrede2=&otherparam=xyz and
>it is always arbitrary if RHTMLO makes it '' or undef.
>That's why I suggested a RDBO column attribute.
>Ron's suggestion to make it NOT NULL would also solve the problem but
>then I would have to prepopulate every optional field with '' -- a value
>that is not really meant to be a value but is meant to be empty/undef/NULL.
>I like it when the schema reflects my intention, so that NOT NULL really
>means "Don't leave this empty", this is why I don't like to use it for
>fields that are meant to be optional.
>But thanks for the reading suggestion, will try to read it, perhaps I
>get convinced.

Using NOT NULL everywhere possible in SQL is a good thing, mainly in 
that it serves to help us avoid or un-break a mis-feature of SQL, 
which is supporting NULL at all, or at least in the inconsistent way 
that SQL supports it (eg, sometimes NULL=NULL, othertimes NULL!=NULL).

If you have a place that holds a value, it should always hold a 
value.  If you don't know a value for a place or a value isn't 
applicable there, then the place shouldn't exist at all.

The more correct way to do this which SQL supports is through table 
normalization, splitting tables on vertical lines at least such that 
for any given row in each table, all columns/fields can be populated 
with real/normal values at once.  Fields/columns that are only 
sometimes valued are in their own rows of separate tables, so if we 
don't have values to put there, the corresponding rows simply don't 

A logically equivalent method which doesn't require table splitting 
would instead require the DBMS to support table-valued fields, so 
that inner table values could consist of zero rows when the normal 
values aren't known or are applicable, and they consist of one (or 
maybe more) rows when they are applicable; the latter looks the most 
like NULL support without having the problems that SQL NULLs cause.

Not all SQL DBMSs support the latter, but they all support the 
former.  Those that support the latter include PostgreSQL, at least 
in a simpler sense (array-valued fields), and some others.

Also don't forget that the relational model of data doesn't specify 
physical storage details at all; a DBMS can still perform well when 
users do things more logically proper; in fact, the DBMS should be 
implicitly faster due to it being able to do more optimizations that 
would otherwise be unsafe in the presence of NULL or other bad 
logical design.

-- Darren Duncan

SF.Net email is sponsored by:
Check out the new Marketplace.
It's the best place to buy or sell services
for just about anything Open Source.;164216239;13503038;w?
Rose-db-object mailing list

Reply via email to