On Thu, 2009-03-12 at 11:32 -0400, James B. Byrne wrote:
> The basic issue is episodic duration, expressed as columns named
> dt_effective_from and dt_superseded_after. Both are datetime types
> containing values normalized to utc. You see where this is going.
I assume you're concern is NULL for the "superseded_after" field and not
the "effective_from" field.
Specifying an "unknown" superseded_after date is not really expressing
what you really want to express. First of all, if it is not superseded,
that is not the same thing as "it has been superseded, I just don't know
when it was superseded". Second, even if you know that it will be
superseded at some point in the future, you know (at a minimum) that
will be superseded beyond the effective_from date, so "unknown" is not
expressing everything that you do know.
An approach you might consider is horizontal partitioning, that is, use
two tables, one for episodes that are complete, and another for episodes
that are in progress. This will allow you to express everything that you
do know, and it's also convenient for writing queries that are easier to
understand. Additionally, it has the nice property that queries on
in-progress data will be more efficient.
You can use NULLs if there's some reason you want to combine the two
tables, but then you have to be careful to not use NULLs for other
purposes (e.g. be careful when using outer joins or NULL to really mean
"unknown" or "not applicable" in some other sense). In other words, if
you do use NULL, you are actually imparting more meaning on NULL than it
would ordinarily have, so you have to be careful not to confuse things.
I found the book "Temporal Data and the Relational Model" by C.J. Date,
et al., very helpful. There's a section about this problem specifically
in the book, but I don't have it nearby so I don't have a page number.
Regards,
Jeff Davis
