need suggestions for table design and relationships

[leeroi]

I am setting up a database which will hold information about community resources across 100 counties in the state of N.C. I have a table with agency information (tblAgencies). Each agency may provide services in multiple counties.

In the past, when working from a 5 county geographic area , I've included the names of Counties as separate yes/no fields within tblAgency. If for example, ACME Speech Services provided services in Jones, Wake, and Lenoir Counties, then checkboxes for those counties would be checked, while boxes for the other counties would remain unchecked.

This approach worked well for a small number of counties, but it seems very awkward to list all 100 counties as fields within TblAgencies.

I've also set up a TblCounty table with county names, and linked this to a CountyName field in tblAGencies. But again this only works where the agency is confined to providing services in one county.

I would appreciate any suggestions on how to handle this situation so that users can easily sort agencies by County.

Thank you,

Lee

 

[jrbarnett]

Have an AgencyCounty table with the primary keys of each source table set up in this table as a composite primary key, being foreign keys from their source tables.
That way, each county can have 0 to as many agencies as needed, with no wasted space in tables.

John

 

[leeroi]

Hmmm. I'm confused. Could you clarify your previous response? So I have tblAgency, tblCounty and tblAgencyCounty? If the primary key for tblAgency is AgencyID, and the primary key for tblCounty is CountyID, then the multiple primary keys for tblAgencyCounty would be AgencyID and CountyID? Would there be other fields for tblAgencyCounty?

Thank you.

 

[lespaul]

You are asking about a Many to Many relationship between counties and agencies. Each county can have many agencies, each agency can service many counties. So you need a "middle" table that can show that:

tblAgency
AgencyID (PK)
AgencyName

tblCounty
CountyID (PK)
County Name

tblCountyAgencies
CountyID
AgencyID (both of these combined to make a composite PK which makes sure that a single county doesn't have the SAME agency multiple times)

you may want to have the address and phone number of that agency's location in the county (since that is a piece of information that is about the agency in that county)

Leslie

 

[leeroi]

Thank you. That works for me!