eliminating many to many relationships

[ljscott]

hello,

i am working on a database that needs to be searchable. i am having a problem with figuring out how to split up all of the data. i have a list of organizations that are associated with certain counties. these counties have multiple organizations located in them. it needs to be possible to seach by both county and by organization.

is it possible to eliminate these many to many relationships? i was thinking about adding a couple of junction tables to do this, but i'm don't think it will work. any ideas?

thanks

 

[randysmid]

Hi,
It looks like you need 3 tables.
Table 1: Counties (County_ID, and County_Name)
Table 2: Organizations (Org_ID, Org_Name, etc.)
Table 3: County_Org
This is the important one!!!!
Key: County_ID and Org_ID)
With this design, Red Cross could appear in any county, or none at all. Plus, the local address for the organization would be stored in the County_Org table, whereas the national (or state) headquarters for the organization would be kept in the Organizations table.

HTH,
Randy Smith, MCP
California Teachers Association

 

[GingerR]

i agree with randy. how come you want to 'eliminate these many to many relationships"??

after you have it set up as randy suggests, you have all kinds of options to search. what kind of searching do you need to do? spell it out here and we'll help (i.e. "Someone will need to select a county and view all organizations"; "Someone will need to pick an organization and see what counties it is in"; "Someone will want to pick a county from a drop-down box, then another drop-down box will be populated with the organizations available in that county".....)