PHP zipcode/city/state database update

[GregL83]

Hello all    ,

 

I am building a PHP/MySQL driven site.  I want to purchase annual access to a zipcode/city/state database.  I have a table that stores records with location information that should be relatively static.

 

I am having trouble developing the relational database structure...  The problem occurs with an update of the zipcode database.  How do relative fields, from other tables, stay current?

 

Example:

 

Location Table

id	location_id
A	1
B	2
C	1

Zipcode Table

id	zipcode	city	state
1	02134	boston	ma
2	02445	brookline	ma

 

What happens when the zipcode database update has an additional zip code that is before the two in my example?  Also, what if boston and brookline were to trade zipcodes (unlikely, but in theory)...  The sample zipcode tables I have looked at do not come with the ID column.

 

My general question is:  How do you maintain zipcode/city/state table(s) that have related records in another table?  Remember the zipcode updates.....

 

Thanks and sorry for the long post..

[F1Fan]

The ID column should be an auto-incrementing unique number. Then, if you wanted to update Boston's zip code, you reference the ID to do the update. Then, in your location table, all locations using Boston's ID will automatically be referencing the new zip code once it's updated.

 

As far as inserting new cities, it won't matter the order that they're inserted, only that their ID is unique.

 

Hope that answers your questions.

[GregL83]

Does that mean I need to add logic for every record?  Update or add if necessary...  I was thinking there would be a way to drop all records and insert the newer zipcode database as a replacement.  That would keep the organization of the zipcode database.  There are over 42,000 records for the US.  I could do this with a cron job...

 

Sites that have other countries and provinces do it this way?  With so many records and needing to run logic I feel like there is a better way.

 

A sample database is:

http://www.download.com/USA-5-digit-ZIP-Code-Database-Premium-Edition/3000-2210_4-10679966.html?part=dl-USA5digit&subj=dl&tag=button&cdlPid=10679967

 

That is from http://www.zipcodedownload.com

[F1Fan]

What will keeping it organized do for you? If each record has a unique identifier, what else would you need?

 

Also, if you reorganize it, your references will need to be updated, too.

[GregL83]

How would you determine an update vs. an addition?

For example:

 

Old Table:

1 | 02134 | Boston | MA

 

New Database of Zipcodes (boston changes the city name to bostun):

02134 | Bostun | MA

 

My new table would come out like:

1 | 02134 | Boston | MA

2 | 02134 | Bostun | MA

 

All locations still linking to ID 1 would be wrong (outdated).  Also, old cities (bad data) would be stored??  Or is the zipcode unique to one city?  I thought you could have a zip code that is existent for 2 different adjacent cities...

[F1Fan]

An addition would be an "INSERT," while an update would be an "UPDATE."

 

Before:

1 | 02134 | Boston | MA

 

Addition:

INSERT INTO zipcodes (zipcode, city, state) VALUES (12345, 'some city', 'ma')

 

After:

1 | 02134 | Boston | MA

2 | 12345 | some city | ma

 

Update:

UPDATE zipcodes SET city = 'bostun' WHERE id = 1

 

Final:

1 | 02134 | bostun | MA

2 | 12345 | some city | ma

 

Thus, every row in the locations table will still reference ID 1 for Boston, which is now bostun.

[revraz]

Since Zip Codes are Unique, why not use that as your ID instead?

 

You can also use INSERT with ON DUPLICATE KEY UPDATE parameter.

[GregL83]

The problem is the zipcode databases I have seen DO NOT have unique identifiers.

 

A zipcode can have more than one city.  See the sample download I provided.

 

That rules out using an id to determine if an update is needed.  Also, changes are not listed.

So when:  02134 | Bostun | MA is in the database table instead of 02134 | Boston |MA

You cannot determine that was a change and not an additional city belonging to 02134 zipcode.

 

 

Hence my problem.  Has anyone successfully used zipcode tables to map locations?  And updated the tables?

[F1Fan]

So, in other words, you need to be able to replace an entire table with potentially new data and have another table that isn't being updated at the same time just know automatically that the other table was updated and it should know what row it is supposed to be associated with?

[GregL83]

My question exactly.

 

I have never maintained this kind of database before.  These zipcode databases are available from hundreds of data supplier sites on annual and month to month basis.

 

I am trying to figure out how to update the changes....

[F1Fan]

What you're wanting to do would be extremely complicated, if it's possible at all.

 

I suggest you consider other options. Personally, I would try creating a program that searches you current zip code table and updates where necessary, and inserts where it doesn't exist. Otherwise, re-writing the table completely just to "organize" it will do the exact opposite. Just because they would be in numerical sequence doesn't mean that it's useful. Those ID numbers are only useful if you know what to reference.

[revraz]

Subscribe to one and let them send you updated databases, and just replace yours with the new one.

 

My question exactly.

 

I have never maintained this kind of database before.  These zipcode databases are available from hundreds of data supplier sites on annual and month to month basis.

 

I am trying to figure out how to update the changes....