Re: Proposal - temporal contrib module

On Thu, 2009-10-29 at 00:31 -0700, Scott Bailey wrote:
> Nulls - A common use case for periods is for modeling valid time. Often
> the end point is not known. For instance, you know when an employee has
> been hired but the termination time typically wouldn't be known ahead of
> time. We can either represent these with a null end time or with
> infinity. But I'm not sure how to deal with them. Obviously we can test
> for containment and overlap. But what about length or set operations?

I think we should allow the database designer flexibility here. For
instance, there are good arguments for using separate relations for
things with a known begin time and no known end, and things with a
definite begin and end time. However, infinity also makes sense,
particularly in cases where something that is true can never again be
false.

NULL support is a little stranger. We can allow it by extending the data
representation, but the semantics might not match what people expect
from NULL. Should it be treated like a NULL in an array, or a NULL in a
record value, or what? If we allow NULL on one side of a period, that
may require some backend support, depending on the semantics.

My feeling right now is to not provide a way for one side of the period
to be NULL, but if we come up with clear enough semantics maybe it's
useful. I tend to think it would cause more confusion than anything.

For any kind of built-in audit log functionality (transaction-time
based), I don't see any utility for NULL.

> Temporal Keys - We need two types of temporal keys. A primary key,
> exclusion type prevents overlap so someone isn't at two places at the
> same time. And a foreign key, inclusion type so we can check that the
> valid time of a child is contained with in the valid time of the parent.
> Jeff is working on the former, but there is no easy way to do the latter.

I believe temporal foreign keys can be implemented the same way foreign
keys are now (except with "contained by" rather than "equals"). We
should provide some support to make that easier, but I don't think
that's a major issue.

Regards,
Jeff Davis