Databasen - NULL dates

[Paul Golds]

On 10/18/06, Nigel Rantor <wiggly at wiggly.org> wrote:
> Well, let's say we have:
>
> 1) As yet unknown, will possibly be known in the future (date of death,
> employee resgination)
>
> 2) Unknowable (end of the universe)
>
> If something falls into category 2 then why model it at all? It is a
> constant.
>
> If we're going to start talking about things that depend on company
> policy then you cannot try to model all things that a policy states we
> cannot know because the list would be endless.

Things can fall into the two categories in the same column, though.

For example, suppose I have a simple database table to represent my
email contacts.  I now decide to implement a date_of_birth column to
represent their birthdate so I can send them birthday congratulations
as appropriate, and to make my sideline of ID fraud a lot simpler.

I add various family members to start with, through constant reminders
I know their birthdates and can add them without trouble.

Feeling all smug I now add my friends, although for some of them I
simply don't know their birthdays I leave them NULL ... category 1,
Unknown data.

I now start to add the final set of contacts, for example the
London.pm subscribe address so I can have endless hours of fun
unsubscibing/resubscring.I also leave this NULL, the subscription
service does not have an email address..either category 2 unknowable,
or even a nice shiny new 'Not applicable'.

Here now it'd be useful to distinguish between unknown and unknowable,
especially when I decide to write a list of people to chase up for
their birthdates.. "Hey, Harry, send me your birthday so I can pretend
to be you to your bank?", "Hey, London.pm subscription system, send me
your birthday so I can arrange birthday drinks for you?".

Doesn't quite work as expected here.

Now many people, including myself with a beer or two inside of me,
would argue quite forcibly that this schema was a crazy concoction
produced by one too many nights staring at ponies. The fact remains
though that this is the type of database table seen in most
small-to-medium companies, and possibly even more so in large
companies where the schema isn't passed by any DB heroes for
sanity-checking.  It's the type that matches the OO-programmer's way
of thinking most commonly and hence the type often built as database
backends to webthingies.