On Thu, May 5, 2011 at 4:14 PM, Jack Christensen <jackc@xxxxxxxxxxxxxxxxx> 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@xxxxxxxxx
