What is the best way to handle multiple table relationships where
attributes of the tables at the ends of the chain must match?
Example:
CREATE TABLE achievements(
achievement_id serial PRIMARY KEY,
...
);
CREATE TABLE achievement_versions(
achievement_version_id serial PRIMARY KEY,
achievement_id integer NOT NULL REFERENCES achievements,
...
);
CREATE TABLE achievement_attempts(
achievement_attempt_id serial PRIMARY KEY,
achievement_version_id integer NOT NULL REFERENCES achievement_versions,
...
);
CREATE TABLE actions(
action_id serial PRIMARY KEY,
...
)
CREATE TABLE achievement_attempt_actions(
achievement_attempt_id integer NOT NULL REFERENCES achievement_attempts,
action_id integer NOT NULL REFERENCES actions,
PRIMARY KEY( achievement_attempt_id, action_id)
);
The achievement_attempt_actions table links actions to
achievement_attempts. For a link to be valid a number of attributes of
actions must match attributes of achievements and achievement_attempts.
This means an update to any of these 5 tables could invalidate the
chain. How can I eliminate the possibility for this type of erroneous data?
I have come up with 4 possibilities.
1. Composite keys -- I could include all the attributes that must match
on all the tables through the chain and let foreign key constraints
handle it. This could work but it feels wrong to be duplicating
attributes. It also is inconvenient (but possible) with my ORM.
2. Triggers -- I can use triggers to check every change on all 5 tables
that could possibly cause an invalid chain. I have done this before and
it does work -- but it can be error prone.
3. Check a materialized view -- Add triggers to all 5 tables to keep a
materialized view up to date. Check constraints could validate the
materialized view.
4. Validate application side -- this can work well, but it leaves the
hole of a bug in the application or a direct SQL statement going bad.
Anyone have any advice on the best way to handle this?
--
Jack Christensen
jackc@hylesanderson.edu