Databasen - NULL dates

[Andy Wardley]

Jurgen Pletinckx wrote:
 > Perhaps unknowable was the wrong word. Unknown, then? As in "We know
 > this happened, but can't possibly assign a correct date" for fields
 > where a unambiguous date was assumed, such as blood sample collection.

In general, I'm with the "use NULL dates" crowd.

However, if you have a situation where you really do need to encode some extra
information about a date (or any other field) then you should probably have
another column explicitly providing that metadata. It might be a date_valid
boolean flag, a date_status enum, or perhaps a foreign key to some other table
defining date "states".

A trivial example:

    # fresh blood known to be < 100 days old
    SELECT id, blood_type, sample_date FROM blood_samples
     WHERE sample_date_known
       AND TIMESTAMPDIFF(DAY, sample_date, NOW()) < 100;

This example is a little contrived but hopefully it illustrates the key point.
If you have some "out-of-band" information that's worth saying about a piece
of data, then it's usually worth an extra column to say it explicitly and
unambiguously rather than trying to assign implicit meaning to particular
"in-band" values.

In doing so, you make it a formal part of your data model (however informal
that might be) that you can point to and point others too. Even if you have no
other supporting documentation (and heck, who reads it anyway? ;-), the fact
that it's written there in the schema is a major benefit for someone trying to
figure out your DB some time from now.

In contrast, any "secret knowledge" coded in particular database values (other
than the basic defined/undefined distinction) is effectively hidden from view
until you start digging into queries or reading the documentation.

As Brooks didn't quite say but probably would have done if he had spent more
time hanging around in other people's databases:

   "Show me your database schema and I won't need to read your incomplete and
    wildly inaccurate documentation or so much as glance at your dangerously
    fragile and bug-ridden database abstraction code.  It'll be obvious!"

A