NULL is your friend! (was Re: Basic gadfly question)

[Alex Martelli]

"Steve Holden" <sholden at holdenweb.com> wrote in message
news:#ub8hWv8AHA.277 at cpmsnbbsa07...
    ...
> column of each row must hold a value. In general it isn't necessarily
sinful
> to build databases holding NULL values, but the whole NULL idea, bringing
> triple-valued logic in tow as it does, is full of pitfalls for the unwary.

...but the *LACK* of NULLs is even trickier!  In the real world, you
often may not (or not yet) know what value some entry has/will have
for all of its attributes, yet need to add the entry anyway.  Most
forms on the web, for example, have some mandatory fields (the NOT
NULL columns:-) and several optional ones.  In practice, then, what
people do when a database engine doesn't support NULL (or, more
often, when they've been advised to avoid NULL for some didactical
reason by well-meaning authors and teachers:-) is to pick out some
"magical value" that (they think...) "just can't happen" in the
real data, and *simulate* NULL with it.  Without any intrinsic DB
support -- slow, cumbersome, unsafe (no error checks...), tricky in
the utmost.  Sometimes for example the Price column is set to -1
to mean "price not known yet", where NULL should be used for the
purpose -- since no real-world price can be <0, this is "safe", is
it not...?  Well... not really.  One of these days somebody will
query the AVG(Price) and get a misleadingly low value because all
of the '-1's are being added and counted... if NULL had been used,
AVG(Price) would intrinsically ignore all records with NULL Price.

Who's going to guarantee that every writer of queries always keeps
in mind the need to add the WHERE Price>0 qualification?  Nobody,
that's who.  What IS inevitably connected to pitfalls (because of
*intrinsic* complexity) is that fact that we NEED to have incomplete
records in our DB -- records for which the data are not entirely
known (or, not yet).  Given that source of complexity, NULLs are
the best way to handle it.  Trying to do without NULLs is a very
tiresome and unrewarding exercise.


> You may find that you can use a null string ("") to represent no value. A

...which is exactly what you'd be very well advised not to do:-).
Oh, you may HAVE TO, if you MUST do without NULLs, but it's just
the start of your troubles.  One day you'll join on that field
and get a LOT of "very interesting" results...

> lot depends on the application. As a matter of interest you can download
> enough of the Jet engine to establish ODBC data sources without having to
> buy and load Access itself. Jat, and also the MSDE engine, which IIRC
comes
> as a part of the Office suite and also as a part of VisualStudio, can be
> freely redistributed if you have the right license.

AFAIK (but IANAL), the EULA for Jet lets you freely redistribute
it.  http://support.microsoft.com/support/kb/articles/Q239/1/14.ASP
(last update June 1, 2001, with info on Jet 4.0 SP5, the latest and
maybe last-ever Jet release AFAIK) has all the details -- you need
three different downloads for /2000, for /ME, and for all older OS's
(/95, /98, /NT 4).  Depending on your needs you may also want to
add the "Jet compact" download, and/or MSDAC (the universal Data
Access download, which doesn't bundle Jet any more), but all the
links you need are on the above-mentioned URL anyway.

For MSDE (the SQL Server engine), see the URL:
http://msdn.microsoft.com/vstudio/msde/genfaq.asp
There, you do need to have a valid Professional or Enterprise
license for Microsoft Visual Studio, or Office 2000 Developer,
to "distribute these solutions to end-users royalty free", as
Microsoft puts it.  If you do qualify, for downloading it see
http://msdn.microsoft.com/vstudio/msde/download.asp.
All of this refers to MSDE 1.0, equivalent to SQL Server 7.

I have no personal experience yet with MSDE 2000 (equivalent
to SQL Server 2000), but see URL:
http://www.microsoft.com/sql/techinfo/development/2000/MSDE2000.asp
The licensing seems very similar.  I'm not sure where to
download it from (we got it as part of an MSDN subscription).
It does not support Windows/95 anymore.


> Thanks to Alex Martelli for pointing some of this out to me.

You're welcome!  It's probably worth pointing out once
again that any of these solutions DO lock you into a
Microsoft operating system plaform for deployment, and
that MySQL or PostgresSQL may therefore prove preferable
if one is thinking of cross-platform deployment (either
now or as a future possibility).  SAP DB, Interbase, and
others yet might allow similar ease and freedom of no-cost
cross-platform development & deployment, but I have no
personal experience with them whatsoever.

For didactical purposes, I would also consider Ocelot, at
http://ourworld.compuserve.com/homepages/OCELOTSQL/ -- they
claim full SQL 92 compliance with several SQL 99 parts,
and have a large array of didactical resources.  Alas, their
free product is Windows-only *AND* not freely redistributable
(you'd have to direct your users to visit their site for
free download, an often-impractical approach; or, negotiate
with them for redistribution) -- so, didactical reasons are
probably the only ones to recommend Ocelot (it IS good for
that, for all I can judge so far, though).


Alex