Welcome Guest, Not a member yet? Register   Sign In
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




Theme © iAndrew 2016 - Forum software by © MyBB