Re: How to specify infinity for intervals ?

On Tue, Feb 21, 2006 at 12:24:09PM +0900, Michael Glaesemann wrote:

> I don't know the details of your database schema,
If you want to you can look it up here:

http://salaam.homeunix.com/twiki/bin/view/Gnumed/DatabaseSchema

Feel free to comment !

> but I think the
> relationally proper way to do would be to have a separate table for
> the maximum ages for vaccinations that have them.
I know, but, oh no, yet another table ;-( And it would
also just lead to another form of NULL via left joins as you
point out below. I specifically wanted to avoid that by
something like 'infinite'::interval in some way or other
such that I could always do

"... where now < date_of_birth + max_age ..."

and not need an

"... or max_age is null ..."

in all the places.

> Vaccinations that
> *don't* have a maximum age would not have an entry in the table.
As you notice further down my predicate was wrong, actually.
You found the proper predicate by yourself, though:

"Do not care about the age of the patient when deciding
whether to give this vaccination."

> The "special value" method, e.g., "999 years" is another way of
> indicated a special value, but in this case I think it's a bit
> different. As I see it, the predicate for the vaccination_max_ages
> table is "The vaccination 'vaccination' must be given before the
> patient is 'maximum_age'". Using a special value changes this
> predicate to "The vaccination 'vaccination' can be given at any time
> in the patient's life." As you point out, using a sufficiently large
> interval for maximum_age makes that statement very likely to be true,
> but the predicate is not exactly the same. Not having an entry in
> vaccination_max_ages is much closer to the idea that the vaccination
> has no maximum age.
Well, but there's not really a medical difference between
the two AFAICT.

> That's the theory, anyway. Hope this helps a bit.
It confirms my thinking isn't entirely wrong.

> Currently on the todo list there's a mention of adding infinite
> dates, similar to infinite timestamps. Perhaps infinite intervals
> could be added as well?
I'd be delighted to have that happen.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346