Databasen - NULL dates

[Ovid]

--- Nigel Rantor <wiggly at wiggly.org> wrote:
> We got into a discussion about whether or not to use NULL values for 
> dates that are as-yet-unknown. anyone have strong feelings and
> arguments either way?

If you're going to use NULL for an unknown date, you must be very aware
of the pitfalls involved.

As a trivial example, let's say that you have an orders table and an
invoices table.  Your business rules state that invoice dates must
never be the same as order date and you must never invoice someone on
Christmas.  

  +-------------+
  |    ORDERS   |
  +----+--------+
  | id |   date |
  +----+--------+
  |  1 | 010101 |
  +----+--------+

  +-------------+----------+
  |         INVOICES       |
  +----+--------+----------+
  | id |   date | order_id |
  +----+--------+----------+
  |  7 |   NULL |        1 |
  +----+--------+----------+

So we want to select all invoices where the order date and the invoice
date don't match and where the invoice date isn't Christmas (pretend
this matches your SQL variant):

  select o.id, i.id
  from   orders o, invoices i
  where  o.id = i.order_id
    and  ( o.date <> i.date or i.date <> '25122007' );

That will return no rows, but that's logically incorrect.  Either the
unknown invoice date is Christmas or it's not.  If the invoice date is
*not* christmas, the "i.date <> '25122007'" should return order id 1. 
If it *is* christmas, then "o.date <> i.date" should return order id 1.
 One way or another, from a purely logical standpoint, we know that
order id '1' satisfies our criteria and should be returned.  However,
3-value logic means that we can't make this evaluation and thus returns
no results, even though we *know* we have at least one result.

As your business rules get more complex, the more NULLs you have in a
database, the more likely it is that your queries will return incorrect
results.  More than once I've struggled with this on large systems with
poorly designed databases and it can be *very* hard to track down
(consider that even the above example can involve a lot of
head-scratching).

This isn't to say I haven't used NULL columns in databases, but you
have to try and judge whether potentially incorrect queries will cost
more than the extra effort to avoid NULLs.

Cheers,
Ovid

--

Buy the book -- http://www.oreilly.com/catalog/perlhks/
Perl and CGI -- http://users.easystreet.com/ovid/cgi_course/