Best database design for zip & state city tables

Question

My application will have to reference the addresses. Street information will be stored along with my main facilities, but the rest should be kept separate to reduce redundancy. How to store / extract ZIP files, cities and states? Here are some of my ideas.

single table solution (can not do relationships)

[places] locationID locationParent (FK for location identifier - 0 for status records) locationName (city, state) locationZIP

---

two tables (with relationships, FK constraints, ref integrity)

[state] stateID StateName

[city] CityId stateID (FK for state.stateID) city name ZipCode

---

three tables

[state] stateID StateName

[city] CityId stateID (FK for state.stateID) CityName

[zipper] zipID cityID (FK for city.cityID) zipName

---

ZIP- . . ZIP (ok ), ZIP (oh snap), ZIP ( ) ! ZIP- , , . , ZIP- , .

- ( ) , , ?

Answer 1

, , : " " :

(/)

, . : .

, ( ) .. . , , , , , .. - , , . 99,99% .

Answer 2

( ), USPS , /. . () . :

ZIPCODE nvarchar(5) not null
CITY nvarchar(50) null
STATE nvarchar(2) null
STATECODE nvarchar(50) null
COUNTY nvarchar(50) null
COUNTYCODE nvarchar(50) null

(. )

edit: , zipcode ( ..), zipcodes ..

http://www.usps.com/ncsc/addressinfo/addressinfomenu.htm

: , , . . , .

Answer 3

. , - . , " / ZIP , ?"

: (/) .

. , , . . , .

, , :

[]; stateID; StateName

[]; locationID; stateID (FK); ; zipID

[location.stateID] - "--" [state.stateID]. ZIP , ZIP- . , ZIP , , USPS - , . ​​ ZIP. , ZIP , .

Answer 4

, , .

, , . , , , .

, , , , , .

, ( , ), , , .

.

AI , . . .

Answer 5

- . , , , :

• - ?
• - ?
• - ? , zip- , .
• ? , - , , ..
• ?
• , , ? , , , ..?