How would you implement a db table of a list of all US zip codes in a rails application?

How would migrations be involved? Would you load them into the 开发者_StackOverflowmysql db directly or have a ruby routine doing this.

To get the raw data I'd simply search on Google, you'll find lots of databases of zip codes, some of them are not free though.

Then I'd look at the data to get a clue on what columns I should include in the table, and build an appropriate migration and model.

Then I'd write an external ruby script which reads the data from whatever format the zip code database is in and writes it directly into the app's database. You could also do that as part of your Rails application, but I usually don't consider it necessary when dealing with external data only.

It's important, however, that the zip code table is not referenced by ID in some other table, since that makes it really complicated if you want to update it later (zip codes change). So I'd still store the zip code itself in, say, the user table, or wherever.

Here is a CSV link to all the zipcodes in the US: here . This file has 7 columns for each zipcode, with the zipcode being in the first column.

Now you could use the ruby CSV parser, or something like FasterCSV to parse the CSV, but I think it would be much faster if you simply parsed the CSV using a shell command. For example, I just ran this on my system and it instantly parses the file correctly:

cut -d ',' -f 1 zip_codes.csv > out.csv

At this point, it's a simple matter of reading in the file line by line in ruby like so:

File.open( out.csv ).each do |line|
  Zipcode.create(:zip => line) 
end

You will replace Zipcode.create.. with whatever model you are using, since you probably do not need a seperate Zipcode model.