DB Design: ZIp Codes

[Popgun]

Sorry could not find a specific thread on this, setting up a user database, while normalizing noted that Zip Codes in US will require its own table.

 

Does this structure look right?

 

TABLE zip (

zip_ID INT UNSIGNED NOT NULL AUTO_INCREMENT,

zip VARCHAR(10) NOT NULL,

PRIMARY KEY (zip_ID));

 

This is then linked to:

 

TABLE locations (

location_ID INT UNSIGNED Not NULL AUTO_INCREMENT,

zip_ID INT UNSIGNED NOT NULL AUTO_INCREMENT,

city ENUM NOT NULL,

state ENUM NOT NULL,

country ENUM NOT NULL,

PRIMARY KEY (location_ID))

 

Thoughts/Suggestions?

 

 

 

 

 

[Barand]

As it is, that's a normalization too far.

 

However, were you to store other data related to zip code (such as latitude, longitude etc) then you would have a separate table, but just use the zip itself as the PK.

[The Little Guy]

I would download this:

 

http://www.populardata.com/downloads.html - txt file format.

 

I have it in SQL format, so if you want I could give you the SQL...