On 5/5/2011 2:28 PM, Rick Genter wrote:
On Thu, May 5, 2011 at 3:20 PM, Jack Christensen
<ja...@hylesanderson.edu <mailto:ja...@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.gen...@gmail.com <mailto:rick.gen...@gmail.com>
--
Jack Christensen
ja...@hylesanderson.edu
