From: Jamie Lawrence <postgres@jal.org>
To: Taylor Lewick <tlewick@kc.rr.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: relationship/table design question
Date: Thu, 4 Dec 2003 16:17:45 -0500
Message-ID: <20031204211745.GB18257@clueinc.net> (raw)
In-Reply-To: <001c01c3b947$8cf3cb70$c0341a41@taylor>
References: <001c01c3b947$8cf3cb70$c0341a41@taylor>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: postgres@jal.org, tlewick@kc.rr.com
Subject: Re: relationship/table design question
In-Reply-To: <20031204211745.GB18257@clueinc.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox