Problem of indexing Country, State , City columns

[sunilwije]

Problem of indexing Country, State , City table.
Instead of entering repeated user location for several users who share the same location I am planning to normalize by giving locationID from Locations table to each user in the User table so that I don’t have to enter Country, State, City repeatedly in the User table so I save disk space. (USA, CT, Woodhaven )
After several users say 12th users may enter USA,NY, Albany and this entry is entered in the 12th row in the Locations Table . When a user enters his locations information (Country, State, City) I need to check in Locations table to see if the record exists before entering the new record. Problem is that you can’t index State and City columns because it will not match with the country ( Afghanistan , Alabama, Azirben, Country, State and City respectively.
Is there a EFFICIENT way you can sort the State, and City to be in consistent with alphabetically indexed Country name (I want the State starting with A and the City starting with A in Afghanistan to go with Country Afghanistan as the first row and so on assuming Afganistahn is the first country in country list.
I believe even though the normalized method having a separate Locations table saves disk space, time to search the record , insert if not already in the Locations table and then insert LocationsID in the user table is more costly in terms of time. Am I correct in my assertion?

[Oralloy]

You need to create a multi-field index, something like this:

CREATE INDEX ON table(country, state, city);

Cheers,
Oralloy

[sunilwije]

I know how to create a table but is there a better way than repeating the same user location (Country,State, City) which are common for some users in the User table.

[Oralloy]

sunilwije,

If you are concerned about performance, then you might want to consider the value of using a stored procedure to do your complex record insert process - that way, there is minimal network and application lag in the processing loop.

Also, what will you be querying? It has been my observation that most city names have multiple acceptable spellings and abbreviations, for example:

New York, New York, USA
NYC, New York, USA
NY, NY, USA
etc...

and that is not counting misspellings.

So, unless you have a comprehensive database of locations across the countries you expect to serve, and you plan to keep it updated (new cities are created rather regularly, after all) on a regular basis, you are likely best off keeping the city information directly in the address record. Especially if the user is expected to enter the information by hand.

It really depends on how large you anticipate your database becoming and the queries that will be run.

Personally, I wouldn't create a locations table (with country/state/city), unless there was an overwhelmingly compelling reason to do so. The complexity of the extra processing involved will likely dwarf the value of the data volume saved by normalizing locations.

One compelling reason is if locations were shared among many tables in the database. In that case, it might be best to use full address records, and do your tracking based on an address-id.

Does that help any?

Oralloy