Обсуждение: Multiple table relationship constraints

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

--
Rick Genter
rick.genter@gmail.com

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of Jack Christensen
> Sent: Thursday, May 05, 2011 3:20 PM
> To: pgsql
> Subject: [GENERAL] Multiple table relationship constraints
>
> 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?
>

Not totally following the usage though I have come across similar
requirements before.  A variant of #4 would be to remove
INSERT/UPDATE/DELETE permissions on the relevant tables and write SECURITY
DEFINER functions to perform those actions instead. You can additionally
leave the constraints loose and have the function query the tables
post-modification to make sure they are still valid (kind of like the
materialized view option but without a permanent table).

David J.

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

So your data is denormalized? (The "category" appears in 2 tables?) Don't do that. Create a view that joins your two tables together instead if you need a single entity that contains data from multiple sources. Then you won't have any of the data integrity issues you're worried about.

--
Rick Genter
rick.genter@gmail.com

On 5/5/2011 2:53 PM, Rick Genter wrote:

On Thu, May 5, 2011 at 3:50 PM, Jack Christensen <jackc@hylesanderson.edu> wrote:

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.

So your data is denormalized? (The "category" appears in 2 tables?) Don't do that. Create a view that joins your two tables together instead if you need a single entity that contains data from multiple sources. Then you won't have any of the data integrity issues you're worried about.

It's not denormalized. It is an attribute that both tables have that have to match for it to be a valid link.

Here's a contrived example:

CREATE TABLE dorms(
  dorm_id serial PRIMARY KEY,
  gender varchar NOT NULL,
  ...
);

CREATE TABLE people(
  person_id serial PRIMARY KEY,
  gender varchar NOT NULL,
  ...
);

CREATE TABLE room_assignments(
  person_id integer NOT NULL REFERENCES people,
  dorm_id integer NOT NULL REFERENCES dorms,
  ...
);

Men should only be assignable to men's dorms and women should only be assignable to women's dorms. On occasion a person's or dorm's gender needs to be updated. I want to make sure that doesn't cause a room assignment to become invalid. In this example, adding gender to room_assignments and using composite foreign keys is fairly straight forward -- but in my actual domain I have 5+ tables and 2+ attributes involved in the relationship.

--
Rick Genter
rick.genter@gmail.com

-- 
Jack Christensen
jackc@hylesanderson.edu

--
Rick Genter
rick.genter@gmail.com

I think the best way is what David has suggested...

On 5/5/2011 3:26 PM, Rick Genter wrote:
>
> Hm. I think the way I would handle this is to put the business logic
> for inserting/updating into the room_assignments table into one or
> more functions and have a special user that owns the tables and owns
> the functions and declare the functions to be SECURITY DEFINER. Revoke
> INSERT/UPDATE/DELETE access to the tables from all other users. Then
> you grant your regular users EXECUTE access to the functions. The
> functions run as the user that created them, so they will have direct
> INSERT/UPDATE/DELETE access to the tables while your regular users won't.
>
Thanks everyone for your advice. I think this type of approach will be
very helpful.

--
Jack Christensen
jackc@hylesanderson.edu