Re: Interesting question and debate on ddl tables with date fieldsthat will not always have a value

rob@xxxxxxxxx wrote:

But I really thought that a few practical examples have been given. Like the example I gave of the MIN(date).

Sorry about that. I think I missed the point of that email. Since you've taken to omitting the statements you're replying to, I occasionally lose track of the thread.

But looking back at the message in question, I see your point, but that really goes along with what I said: if you have a business need for processing or aggregating sets of data in such a way that the null will be useful, then by all means do so. Me, personally, I don't see the practical need for determining the earliest date that someone was fired, although clearly if you used another scenario, the ability to find the minimum non-null value could be useful.

I'm less sure, though, that MIN(DATE) is *that* much better than MIN(DATE) where STATUS = 'X'. Certainly the former doesn't give you any clue that records exist with nulls and are being excluded. YOu might assume that, obviously, but it's not explicit.

In any case, it's nits. I think there are certainly situations where NULLs are advantageous, but that they're far less than some might have us believe. And thus the use of NULL comes back to a business decision, as such things usually are.

I do find this discussion just a little amusing, in a nostalgic way. It's actually a reprise of the old argument about "strategic denormalization", a term I coined during my SSA tenure to identify the practice of purposely storing denormalized data (typically summary information of 1-to-many relationships) in order to improve performance and/or reduce programming complexity. The same considerations apply here as they did back then: in this case, if inclusion of NULL processing for a field actually reduces the programming load and/or improves performance, then it should be considered.

Joe