Re: relationship/table design question

On Tue, Dec 02, 2003 at 20:44:04 -0600,
Taylor Lewick <tlewick(at)kc(dot)rr(dot)com> 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

That depends on whether the relationship is many to one or many to many.
If it is many to one, you can just make a reference from the event table
to the contact table. If events can have multiple contacts who can be
contacts for multiple events, then you need to create another table
that lists unique pairs of events and contacts.

> going to seperate tables? Finally, can I make the reference constraint be
> 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. Just don't put a not null constraint on column and things will work
like that.

>
> 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.
>
> Thanks,
> Taylor
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly