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.