Re: [GENERAL] Multiple table relationship constraints
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 ja...@hylesanderson.edu -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Multiple table relationship constraints
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 pol...@yahoo.com -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- ow...@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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 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 ja...@hylesanderson.edu -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Multiple table relationship constraints
On Thu, May 5, 2011 at 3:20 PM, Jack Christensen ja...@hylesanderson.eduwrote: 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.gen...@gmail.com
Re: [GENERAL] Multiple table relationship constraints
-Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- ow...@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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Multiple table relationship constraints
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
Re: [GENERAL] Multiple table relationship constraints
On Thu, May 5, 2011 at 3:50 PM, Jack Christensen ja...@hylesanderson.eduwrote: 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.gen...@gmail.com
Re: [GENERAL] Multiple table relationship constraints
On 5/5/2011 2:53 PM, Rick Genter wrote: On Thu, May 5, 2011 at 3:50 PM, Jack Christensen ja...@hylesanderson.edu mailto:ja...@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.gen...@gmail.com mailto:rick.gen...@gmail.com -- Jack Christensen ja...@hylesanderson.edu
Re: [GENERAL] Multiple table relationship constraints
On Thu, May 5, 2011 at 4:14 PM, Jack Christensen ja...@hylesanderson.eduwrote: 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.gen...@gmail.com
