From: | David Pratt <fairwinds(at)eastlink(dot)ca> |
---|---|
To: | pgsql-admin(at)postgresql(dot)org |
Subject: | Question of Table Design and Foreign Keys |
Date: | 2005-05-31 13:08:55 |
Message-ID: | 243B812C-D1D5-11D9-AEE7-000A27B3B070@eastlink.ca |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
);
From | Date | Subject | |
---|---|---|---|
Next Message | ghady rayess | 2005-05-31 14:17:34 | error in installation |
Previous Message | rohan rasane | 2005-05-31 09:28:43 | Starting postgres server second time using admin rights... |