Multiple table relationship constraints

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...)
--
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 <mailto: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 <mailto:rick.genter@gmail.com>

--
Jack Christensen
jackc@hylesanderson.edu

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.

--
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 <mailto: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 <mailto:rick.genter@gmail.com>

--
Jack Christensen
jackc@hylesanderson.edu

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

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.

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.

--
Rick Genter
rick.genter@gmail.com

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

But if it is already live, and there is no way to handle clients app to work
with functions (instead of Direct SQL statements) then I think trigger
function would help... (not sure how it could be error prone..)

So basically if function is

Validate(input parameters)

Insert/Update data

Inside trigger function call the same check Validation function(s) and then
if it returns false - return NULL, otherwise return NEW

Kind Regards,

Misa

2011/5/5 David Johnston <polobo@yahoo.com>

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