One to many relation base

[SGBotsford]

I’m setting up a database for volunteers and agencies.

I want a table – Agency
with things like name, and website, and address and all that good stuff.

I want a table – Contacts
with the usually data you want about people: name, phone, email, etc.

A contact can work for several agencies.

An agency can have multiple contacts for each role in the agency.

I want to minimize redundency and re-entry.

Let’s take it one level at a time:

How do I set up the one to many relation from Agency to contacts?

How then do I modify this so that I have bidirectional one to many so that I
can (in separate queries) fine out what agencies a contact works for, and what contacts fill a given role for an agency?

[TonyP]

This is a database design problem! Without more information about the data its is hard to give a definitive answer, but what you may want is a third table, call it AgencyContacts, which may just contain Agency ID and Contact ID, that expresses the relationship between Contacts and Agencies. So the actual Agency table doesn’t contain reference to Contacts neither does the Contact table contain references to Agencies.

Does that help?