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(at)jal(dot)org
The bureaucracy is expanding to meet the needs of an expanding bureaucracy.