On 5/5/2011 2:28 PM, Rick Genter wrote:
On Thu, May 5, 2011 at 3:20 PM, Jack Christensen
<jackc@hylesanderson.edu> wrote:
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 might not be understanding your question, but isn't that what your foreign key references do? For example, you can't update achievement_attempt_id in the achievement_attempt table if there is an achievement_attempt_actions record that refers to it since that would break the reference. (Not that you want to be updating primary key values in the first place...)
The trick is there are additional attributes of actions and achievements such as a category that must match for the link to be valid. These attributes are not part of the primary key of either record and can and do change.
--
Rick Genter
rick.genter@gmail.com
--
Jack Christensen
jackc@hylesanderson.edu
