I think the way you want to look at it is: if a record exists in sample_attributesjoin, then it needs to reference both samples and attributes. You don't need to worry about samples or attributes being "off" because they don't have "enough" references, instead you want to ensure that if a record exists (whether inserted, updated, or references are deleted) in sample_attributesjoin that the sample_id and attribute_id are always valid. I'd advise to create foreign keys from sample_attributesjoin to samples and attributes, cascade on delete and update as the record in sample_attributesjoin would be invalid if either one of its pointer records were to be removed. Something like: ALTER TABLE sample_attributesjoin ADD CONSTRAINT fk_sa_samples FOREIGN KEY (sample_id) REFERENCES samples (id) ON UPDATE CASCADE ON DELETE CASCADE; ALTER TABLE sample_attributesjoin ADD CONSTRAINT fk_sa_attributes FOREIGN KEY (attributes_id) REFERENCES attributes (id) ON UPDATE CASCADE ON DELETE CASCADE; Jason -----Original Message----- From: pgsql-admin-owner@xxxxxxxxxxxxxx [mailto:pgsql-admin-owner@xxxxxxxxxxxxxx]On Behalf Of David Pratt Sent: Tuesday, May 31, 2005 8:09 AM To: pgsql-admin@xxxxxxxxxxxxxx Subject: [ADMIN] Question of Table Design and Foreign Keys I have a few tables that I want to join. My example is below. What I am trying to figure out is what is better to do for data integrity. I will be joining the samples table with the sample_attributes table to obtain the attributes of a sample. The attribute id will reference an attribute in the attributes table. When a person adds a record in samples, they may not want to add attributes right away. I will be setting up Foreign Key constraints for sample_id and attribute_id in the sample attributesjoin. Question is if a person does not want to add attributes to their sample record right away there will be no reference to it in sample_attributesjoin. Is that ok ? Or should use a trigger to always make at least one record in sample_attributesjoin after a sample record is made and not place a Foreign Key constraint on attribute_id in sample_attributesjoin so it can be empty. I guess I am wondering what is better. Regards, David For example; CREATE TABLE samples ( id SERIAL, title TEXT NOT NULL, description_id INTEGER, type_id INTEGER, language_id INTEGER, notes_id INTEGER, created TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(), modified TIMESTAMP WITH TIME ZONE ); CREATE TABLE attributes ( id SERIAL, attribute VARCHAR(50) NOT NULL, description TEXT, created TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(), modified TIMESTAMP WITH TIME ZONE ); CREATE TABLE sample_attributesjoin ( id SERIAL, sample_id INTEGER NOT NULL, attribute_id INTEGER NOT NULL ); ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your message can get through to the mailing list cleanly
