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