Re: relationship/table design question

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.