Re: Question of Table Design and Foreign Keys

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(at)postgresql(dot)org
[mailto:pgsql-admin-owner(at)postgresql(dot)org]On Behalf Of David Pratt
Sent: Tuesday, May 31, 2005 8:09 AM
To: pgsql-admin(at)postgresql(dot)org
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(at)postgresql(dot)org so that your
message can get through to the mailing list cleanly

Hi Jason, many thanks Jason for your reply.

On Tuesday, May 31, 2005, at 11:25 AM, Jason Minion wrote:

> 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.
>

Yes, this is what I was thinking but just needed to ensure this was the
right approach since I have a number of tables and similar
circumstances. So if there is no attributes for a sample record when I
join the tables and query, I will just have nothing returned for these
records and no trouble with integrity, am I correct? When I up update
or delete records that I have added to attributesjoin, the referential
integrity of attributes and samples table is maintained using the
constraints, have I got this right?

Regards,
David