Re: 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
> <jackc(at)hylesanderson(dot)edu <mailto:jackc(at)hylesanderson(dot)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(dot)genter(at)gmail(dot)com <mailto:rick(dot)genter(at)gmail(dot)com>
>

--
Jack Christensen
jackc(at)hylesanderson(dot)edu