Ask Your Question
0

one to many relation base [closed]

asked 2013-01-19 05:16:14 +0200

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

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?

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2015-10-20 14:02:26.034051

1 Answer

Sort by » oldest newest most voted
1

answered 2013-01-19 16:38:52 +0200

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

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?

edit flag offensive delete link more

Question Tools

Stats

Asked: 2013-01-19 05:16:14 +0200

Seen: 987 times

Last updated: Jan 19 '13