Date: Thu, 4 Dec 2003 16:17:45 -0500
From: Jamie Lawrence <postgres@jal.org>
To: Taylor Lewick <tlewick@kc.rr.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: relationship/table design question
Message-ID: <20031204211745.GB18257@clueinc.net>
Mail-Followup-To: Taylor Lewick <tlewick@kc.rr.com>, pgsql-sql@postgresql.org
References: <001c01c3b947$8cf3cb70$c0341a41@taylor>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <001c01c3b947$8cf3cb70$c0341a41@taylor>
User-Agent: Mutt/1.3.28i
Organization: clue inc

On Tue, 02 Dec 2003, Taylor Lewick wrote:

> Hi all, new to postgresql and I have a question about how to lay out my
> database.
> 
> I have a database with 4 main tables, organizations, contacts, events,
> grants. My thinking here is that organizations can have contacts, sponsor
> events,
> and sponsor grants, so it is what I would call
> the main table.  I know how to set up the other tables so that they have a
> reference back to org_id.  (Basically a foreign key).
> 
> How would I handle it (set up in SQL) if I want to be able to assign a
> contact to an event.  In the event table, would I make a reference back to
> the contact table?  Can I have more than one foreign key reference per table

Probably not what you want to do.

> going to seperate tables?  Finally, can I make the reference constraint be

No, but see below. (Well, you could using an array or a marshalled
field, but I can't imagine why you'd want to.)

> able to be NULL.  Meaning, can I make it so that an event can, but doesn't
> have to have a contact assigned to it?

Yes.

> The tables (a simplified view) would look like this...
> 
> Organization Table:
> org_id
> org_name
> 
> Contact Table:
> contact_id
> contact_name
> 
> Event Table:
> event_id
> event_name
> reference back to org_id in org table
> reference back to contact_id in contact_table but there doesn't have to be a
> contact associated with this event.

Hi there -

In general, I would do something along these lines:

create table organizations (
	id serial primary key, 
	organization text );

create table contacts (
	id serial primary key, 
	name text );

create table events (
	id serial primary key, 
	name text);

create table events_contacts (
	id serial primary key, 
	events_id int references events, 
	contacts_id int references contacts );

In order to associate a contact with an event, you insert a row in
events_contacts with the appropriate ids of the event and the contact.
Some people call this a join table, others a mapping table, others
[insert 15 other names for many to many relations].

You'll have to decide how updates and deletes should behave for your
purposes, and add in those clauses to the foreign key declarations.

HTH,

-j

-- 
Jamie Lawrence                                        jal@jal.org
The bureaucracy is expanding to meet the needs of an expanding bureaucracy.