SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


State County City Problem


State County City Problem

Author
Message
cdun2
cdun2
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1899 Visits: 291
Im dealing with address data, and based on the schema I have, I can't query to answer the following question: 'What county(s) is Birmingham, AL in?'



Here is what I have:



State2 table:

StateID (Identity, primary key),

Name (full state name)

TwoLetterCode ('AL')



County2

CountyID (Identity, primary key),

Name (full county name),

StateID (from the State2 table)



City

CityID (Identity, primary key)

Name (full city name),

StateID (from the State2 table)



Foreign keys:

County2.StateID to State2.StateID

City.StateID to State2.StateID



It so happens that Birmingham, AL is in two counties (so cities can cross county lines). In our data, there is one CityID for each StateID. Does the countyID need to be in the City table?



What do I need to be able to determine what county(s) that a city resides in?



Thank you for your help!



CSDunn



Ninja's_RGR'us
Ninja's_RGR'us
SSC Guru
SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)

Group: General Forum Members
Points: 68517 Visits: 9671
You need one extra table called CityCounties
CityID
CountyID


That's easier said than done. Doing that work for the entire US is not a small task. I'm guessing you'll have to buy that data somewhere.
Gary Johnson-259336
Gary Johnson-259336
Mr or Mrs. 500
Mr or Mrs. 500 (573 reputation)Mr or Mrs. 500 (573 reputation)Mr or Mrs. 500 (573 reputation)Mr or Mrs. 500 (573 reputation)Mr or Mrs. 500 (573 reputation)Mr or Mrs. 500 (573 reputation)Mr or Mrs. 500 (573 reputation)Mr or Mrs. 500 (573 reputation)

Group: General Forum Members
Points: 573 Visits: 184
Been there, done that. Have fun! Smile


Create Table dbo.Region
(
RegionID int identity(1,1) NOT NULL,
RegionName varchar(255) NOT NULL,
RegionType tinyint NOT NULL
)

Create Table dbo.RegionType
(
RegionTypeID tinyint NOT NULL,
RegionTypeDesc varchar(50) NOT NULL
)

Create Table dbo.RegionHierarchy
(
RegionIDParent int NOT NULL,
RegionIDChild int NOT NULL
)

INSERT INTO RegionType VALUES(1, 'State')
INSERT INTO RegionType VALUES(2, 'City')
INSERT INTO RegionType VALUES(3, 'County')
INSERT INTO RegionType VALUES(4, 'Zip')




You get the idea. I put records going both directions in the Hierarchy table.

You have to realize that Zipcodes can reside in multiple Counties, and Cities. Cities can reside in multiple States and Counties.

I also ended up creating separate City, State, and County tables as there were specific attributes for each of these Regions that I didn't put in my basic schema above. While this is in some ways duplicating the data, having a single RegionID for each region has been very benificial.

Gary Johnson
Sr Database Engineer
ms8588
ms8588
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 4
Gary provides a simple and effective solution. He also states that he also has added additional tables to handle unique entity data. There may be a couple more issues you may want to think about. First, I am not sure what you actually need this structure for. How you intend to use it may dictate some of your solution. Consider what is unique here. Only zip codes. Yes States are also unique but their names are not. They may also be used as county and city/town names. Counties are similar. They are unique to a state but Counties with the same name are found in multiple states. There names also show up as cities/towns. City names will show un in many states and on rare occassions can show up more than once in the same state. If I have not given you enough to think about Louisanna does not have counties. They have parishes so you may want/need an identifier for that, just something else to think about. Instead of using an identify column (I assume generated) as the primary key, you might consider using something like a geocode or Dunn&Bradstreet identifier as your primary key to guaranty uniqueness. In the long run this may be simpler. You can then relate them hierarchically, rationally or both. Smile
GSquared
GSquared
SSC Guru
SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)

Group: General Forum Members
Points: 58715 Visits: 9730
Another thing to keep in mind, in a system like this, is that Texarkana (for example) is one city in two states. They have a street called, appropriately, "State Line".

I get a flat-file of city, state, county, Zip, information from www.zip-codes.com. It's cheap and does for what I need. You might want to denormalize that way.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Rautakallio
Rautakallio
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 4
There are a few cities and towns in the US that are not in any county. The county fabric has holes. If I recall correctly the county fabric in Virginia is like this. One or two other states may be like this too.

BTW if you're dealing with a street address it's going to be in one county or another. But one side of a street could be in one county and the opposite side in another.
Michael Valentine Jones
Michael Valentine Jones
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14745 Visits: 11848
Another thing to be aware of is that there are some zip codes that have addresses in two states.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218223 Visits: 41995
Hiearchy can be a good thing... but I don't believe the op can change the structure of the tables. Wink


CS... Please pardon me if I'm wrong, but this looks a whole lot like a homework problem. You'd probably get a better response if you posted what you've tried, so far.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
peerless
peerless
SSC Veteran
SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)

Group: General Forum Members
Points: 230 Visits: 112
do you have a table structure for

coutries

states/provinces

cities
peerless
peerless
SSC Veteran
SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)

Group: General Forum Members
Points: 230 Visits: 112
do you have a table structure for

coutries

states/provinces

cities
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search