On 5/5/2011 2:28 PM, Rick Genter wrote:
On Thu, May 5, 2011 at 3:20 PM, Jack
Christensen <jackc@xxxxxxxxxxxxxxxxx>
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@xxxxxxxxx
--
Jack Christensen
jackc@xxxxxxxxxxxxxxxxx
|
