Skip site navigation (1) Skip section navigation (2)

Question of Table Design and Foreign Keys

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 (view raw or flat)
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
);

In response to

pgsql-admin by date

Next:From: ghady rayessDate: 2005-05-31 14:17:34
Subject: error in installation
Previous:From: rohan rasaneDate: 2005-05-31 09:28:43
Subject: Starting postgres server second time using admin rights...

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group