Null Values

What does a null value in a table actually mean?

Well, technically, a null value means that there is no data for this column.  If the column is to capture a birth date, then a null value would mean that you don't know the birth date.  If the column is about the date of death, then a null value would mean that you don't know the date of death.  It does not mean that the person is alive, just that we don't know the date of their death.

One of the more common problems that developers have is that they make a piece of data, or the absence of the data, mean more than it should.  In the above case, if you need to know if the person is dead, you need an additional field ("Deceased"?) that indicates if the person has shuffled off this mortal coil.  The absence of data in the data of death field cannot, under any circumstances be construed as a field indicating that the person is alive.  What if you were told this person was deceased, but you weren't told when?  What do you do?  Put in a fake date of death?

I have a personal pet peeve in this area.  Within the organization(s) we have a number of tables that have effective dates.  There is a start date and an end date.  What many applications have done is put in "2999-12-31 11:59:59 PM" as the effective end date.  (Historical background: prior to more recent releases of Access, this was the maximum date that Access would allow in a date/time field.)  What this means, to me, is that this record will no longer effective as of that date.  We seem to know this in advance.  Indeed, much of the data that we have seems to expire on this date.  I would not want to be in application support on the day after when all of the data in the organization suddenly expires.

Is this truly the effective end date?  No, it's not.  The effective end date is actually null, but this makes coding for the programmers a little more complicated.  It makes the data cleaner and more accurate, but makes it more difficult to program.

I have a personal preference in this area, as I'm sure you can tell, but I will leave it up to you, the reader, to examine the pros and cons and make up their own mind.  Or, if you'd like, wait until the next Daily Migration Note where a potential solution is revealed.