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
