Database Design

[Lou]

Hello,

I am trying to design a database that consist of 67 counties, their cities,
6 regional offices and the distance from the regional offices to the county
offices.

Here is the other situation. Each month the regional offices have cases
divided among them (ex: 95, 94 it varies). What I would like to happen is
that I be able to enter the number of cases each regional office receives for
the month. Then once I enter the number of cases the county has for that
month that it would be placed in the regional office that is closest to that
county until all regional offices receive the alloted number of cases.

Hope I explained enough.

Thanks!

 

[David F Cox]

one way to tackle this is to enter the X Y map coordinates for each office
and use Pythagorus theorem to select the closest.

Distance = square root of ( (Xa - Xb) squared + (Ya-Yb) squared )

(You do not have to take the square root in order to determine the closest)

 

[Lou]

I am a little green at this. Can you suggest any primary keys so I can
create relationships if necessary. Example
CountyID
CountyName
City


Region ID
Region Name
City
Distance

and what relationship I can create between the two.

Thanks