Thread: Implementing time constraints

I have a table to inventory trees, that looks something like this:

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)
);

The natural key here is the combination of location and time, which needs
to be enforced with a trigger function.

Now I have a table recording notes about the tree, something like this:

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.

And going back to my tree table, I need to update that trigger to ensure
no note_dates fall outside the tree_install_date and tree_removal_date
should they be updated.

Add a few more tables, and before long I have a bunch of triggers
cross-referencing other tables when ever I insert or update a tree.

I've read several back-and-forth discussions arguing whether or
not stored procedures and triggers in databases are "evil" or "a good
thing". In this case, since I'm dealing purely with data integrity
issues (as opposed to business logic), I'm not overly concerned with
writing these triggers. 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?

In short, my noob questions are:

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

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

Any advice/reassurance is appreciated! Thanks.


Karl Nack

Futurity, Inc.
773-506-2007

On Fri, May 29, 2009 at 1:23 PM, Karl Nack <karlnack@futurityinc.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

On Fri, May 29, 2009 at 1:36 PM, Richard Broersma
<richard.broersma@gmail.com> wrote:
> No CHECK CONSTRAINTS required.

I meant to say No CONSTRAINT TRIGGERS required.  Sorry for any confusion.


--
Regards,
Richard Broersma Jr.

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