Re: Multiple table relationship constraints

[Jack Christensen <jackc@xxxxxxxxxxxxxxxxx>]

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

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

-- 
Jack Christensen
jackc@xxxxxxxxxxxxxxxxx