Re: Implementing time constraints

On Fri, May 29, 2009 at 1:23 PM, Karl Nack <karlnack(at)futurityinc(dot)com> wrote:

> CREATE TABLE tree (
>    tree_id             SERIAL PRIMARY KEY,
>    tree_species_id     INT NOT NULL REFERENCES tree_species,
>    tree_location       POINT NOT NULL,
>    tree_install_date   DATE NOT NULL,
>    tree_removal_date   DATE,
>    CHECK (tree_removal_date > tree_install_date)
> );
>
> CREATE TABLE tree_note (
>    tree_id     INT REFERENCES tree,
>    note_date   DATE,
>    note_text   TEXT NOT NULL CHECK(length(note_text) > 0),
>    PRIMARY KEY (tree_id, note_date)
> );
>
> I need to check that the note_date falls within the tree_install_date and
> tree_removal_date of the tree it references -- another trigger.

> But as my triggers get more complex, it does make me pause a minute to consider
> my design -- am I doing a lot of manual work for something that, if better
> modeled, _could_ be handled natively by the database?

> -- Are there any standard strategies for dealing with time-related integrity
> constraints like these? Am I basically on the right track here?

One way to handle this is through the use of a composite
primary/foreign keys with ON UPDATE CASCADE turned on. This is
essentially de-normalizing your tables but maintaining data integrity
through referential integrity. Do this allows you to put the start
and end dates in your tree_note table as part of a composite foreign
key. Now you only need to use an ordinary between check statement.
No CHECK CONSTRAINTS required.

> -- In general, should the fact that I need to write a bunch of triggers to
> enforce data integrity raise alarms about my schema design?

Usually yes, but there is no real facility built into ANSI-SQL to
manage temporal database design. If there was, the RDBMS would
actually be creating these "triggers" in the background for you as
part of your table DDL statements as is already being done in the case
of ordinary foreign keys.

--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug