0

From the 50 states of US, most of them have counties except Louisiana and Alaska.

My tables would look like this

**State_tbl**
State_id
State_name

**County_tbl**
County_id
State_id ->state_tbl
County_name

**City_tbl**
City_id
County_id ->county_tbl
City_name

However, since the two states Alaska and Louisiana don't have counties, I would have problems implementing them. And I also read that there may be cities within a state that don't have a county, or that belong to two counties (don't know if that is true).

What would be the best approach to design the database?

UPDATE More info:
I have an user which would register to serve into specific cities (within a state). When I retrieve data I want to be able to display both the cities that are served, as well as the counties. There would be a Many-to-Many relationship between the user and the cities served, and a one-many relationship between cities and counties.

i.e:
John K - serving in state_A (all counties and cities below belong to state_A)
-cities served: City_A (county_x), City_B (County_Y), City_C (County_Y)
-counties served: County_X, County_Y

Also, would I be able to retrieve a user's info and all the cities and counties served within one query?

Cristian
  • 2,390
  • 6
  • 27
  • 40
  • If you need to track counties for some reason, using the same field to track parishes and boroughs for the same reason (whatever it might be) sounds reasonable. As for, say, New York City covering 5 counties, how you design for this is going to depend a lot on what you're doing with the data, which you don't tell us. – Wooble Jun 11 '13 at 15:27
  • Wooble is right. Give us a little more information and you'll get better answers. – Jeffrey Jun 11 '13 at 16:14
  • Added more information. By the answers you gave I am inclined to do the state 1->N county 1->N city tables. Every county belongs to a state and every city belongs to a county (or whatever form of organization). – Cristian Jun 11 '13 at 19:24
  • @Jeffrey Added more info! – Cristian Jun 14 '13 at 19:47

1 Answers1

0

Just treat the boroughs and parishes and counties (or any other naming conventions) as the same thing. The USPS treats them the same and the Census Bureau also treats them the same. Most of the government (and nongovernment organizations) in the US that need to generate any kind of report on counties or statistical areas rely on the MSA or CBSA codes which are based on these units, all of which are referred to as counties - even though they may have other local names.

For Alaska, here are all the boroughs:

ANCHORAGE
BETHEL
ALEUTIANS WEST
LAKE AND PENINSULA
KODIAK ISLAND
ALEUTIANS EAST
WADE HAMPTON
DILLINGHAM
KENAI PENINSULA
YUKON KOYUKUK
VALDEZ CORDOVA
BRISTOL BAY
MATANUSKA SUSITNA
NOME
YAKUTAT
FAIRBANKS NORTH STAR
DENALI
NORTH SLOPE
NORTHWEST ARCTIC
SOUTHEAST FAIRBANKS
JUNEAU
HOONAH ANGOON
HAINES
PETERSBURG
SITKA
SKAGWAY
KETCHIKAN GATEWAY
PRINCE OF WALES HYDER
WRANGELL

For Lousiana, here are all the parishes:

JEFFERSON
SAINT CHARLES
SAINT BERNARD
PLAQUEMINES
ST JOHN THE BAPTIST
SAINT JAMES
ORLEANS
LAFOURCHE
ASSUMPTION
SAINT MARY
TERREBONNE
ASCENSION
TANGIPAHOA
SAINT TAMMANY
WASHINGTON
SAINT HELENA
LIVINGSTON
LAFAYETTE
VERMILION
SAINT LANDRY
IBERIA
EVANGELINE
ACADIA
SAINT MARTIN
JEFFERSON DAVIS
CALCASIEU
CAMERON
BEAUREGARD
ALLEN
VERNON
EAST BATON ROUGE
WEST BATON ROUGE
WEST FELICIANA
POINTE COUPEE
IBERVILLE
EAST FELICIANA
BIENVILLE
NATCHITOCHES
CLAIBORNE
CADDO
BOSSIER
WEBSTER
RED RIVER
DE SOTO
SABINE
OUACHITA
RICHLAND
FRANKLIN
MOREHOUSE
UNION
JACKSON
LINCOLN
MADISON
WEST CARROLL
EAST CARROLL
RAPIDES
CONCORDIA
AVOYELLES
CATAHOULA
LA SALLE
TENSAS
WINN
GRANT
CALDWELL
Jeffrey
  • 502
  • 2
  • 10
  • The link below is a CSV file of all states/counties (parishes,etc), with their corresponding FIPS codes and latitude/longitude centroid, derived from the US Census 2013 gazetteer for counties. Its free for anybody to use: http://www.opengeocode.org/download.php#statecounty – Andrew - OpenGeoCode Dec 15 '13 at 16:38