Thread: relationship/table design question

relationship/table design question

From
"Taylor Lewick"
Date:
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
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?

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


Re: relationship/table design question

From
Bruno Wolff III
Date:
On Tue, Dec 02, 2003 at 20:44:04 -0600,
  Taylor Lewick <tlewick@kc.rr.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@postgresql.org so that your
>       message can get through to the mailing list cleanly

Re: relationship/table design question

From
"R. Kelly"
Date:
 Taylor Lewick wrote:

> Hi all, new to postgresql and I have a question about
> how to lay out my database.

Being new to Postgress is of no consequence. If, however,
you are new to database design, there are many texts on
the web that cover the subject, better and in more detail
than a post such as this could do. A quick search turned
up the following:

http://databases.about.com/library/weekly/aa080501a.htm
http://databases.about.com/library/weekly/aa081901a.htm
http://databases.about.com/library/weekly/aa090201a.htm
http://databases.about.com/library/weekly/aa091601a.htm

Read those, in order. Keep tables in third normal form,
unless you have some good, quantifiable reason for not
doing so.

Congratulations, you are now a database design expert.

cheers,
Roger