Been there, done that. Have fun! 🙂

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.