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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
