Re: How to specify infinity for intervals ?

On Feb 21, 2006, at 3:24 , Karsten Hilbert wrote:

> I am storing the maximum age a vaccination is due in a
> patient (eg. "don't give this vaccination beyond the age of
> 10 years" or some such). Some vaccinations are to be given
> regardless of age.
>
> Up to now I have used NULL to mean "no maximum age". That
> doesn't really feel right and also complicates the SQL
> needed for retrieving data.

I don't know the details of your database schema, 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. Vaccinations that
*don't* have a maximum age would not have an entry in the table. For
example:

create table vaccinations
(
    vaccination text primary key
);

create table vaccination_max_ages
(
    vaccination text primary key
        references vaccinations (vaccination)
    , maximum_age interval not null
);

This may make the SQL a bit more complicated, and you may end up
doing quite a few left joins which will give you NULLs anyway in the
result unless you use COALESCE. From one perspective (though not one
I necessarily agree with), using NULL to represent "no maximum age"
in the vaccinations table is a shorthand for this situation and
reduces the number of joins required.

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.

That's the theory, anyway. Hope this helps a bit.

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?

Michael Glaesemann
grzm myrealbox com