• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Maintaining cities & countries database

#1
[eluser]charlieD[/eluser]
I've downloaded a list of cities and countries that I'll be using in an application I'm developing. It comes in a TXT format (basically a tab separated file), and I've imported this into MySQL.

The CC_FIPS (country code, e.g. 'UK', 'US') column is the unique identifier for countries, and it's also used to relate the city to the country.

There are about 2.2 million cities listed and 267 countries.

My question is whether it's worth adding a numeric primary key/index for cities and countries, and using this as the relation between cities and countries. I'm sure it must be more efficient, does anyone know by how much?

Also, is there an easy automated way to do this, particularly considering the situation where if the database is updated, I'll need to integrate these results. If I don't change the data, I'll be able to easily overwrite it with any updates that come through.

#2
[eluser]Randy Casburn[/eluser]
Hi Charlie,

There are a lot of dependencies that will determine the answer to the efficiency question. Which engine you're using, which indexing method you've chosen, whether you've chosen BINARY data types, the length of your keys, etc. etc. So for this answer...it depends.

The answer to the 'automagic' updates is yes this is done through "foreign" key constraints and cascading updates. MySQL tables can be created so that when a foreign key is established as a link to a primary key in a different table, any changes in the form of deletes, updates, etc. can be Cascaded or ignored by the child tables. This automates the process of keeping your indexing tables up to date for you.

Hope you find this helpful.

Randy


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.