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