Question of Table Design and Foreign Keys

Поиск
Список
Период
Сортировка
От David Pratt
Тема Question of Table Design and Foreign Keys
Дата
Msg-id 243B812C-D1D5-11D9-AEE7-000A27B3B070@eastlink.ca
обсуждение исходный текст
Ответ на Starting postgres server second time using admin rights...  (rohan rasane <rohan.rasane@gmail.com>)
Список pgsql-admin
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
);

В списке pgsql-admin по дате отправления:

Предыдущее
От: rohan rasane
Дата:
Сообщение: Starting postgres server second time using admin rights...
Следующее
От: "ghady rayess"
Дата:
Сообщение: error in installation