471,082 Members | 812 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,082 software developers and data experts.

Problem of indexing Country, State , City columns

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?
May 20 '13 #1
3 1701
983 Expert 512MB
You need to create a multi-field index, something like this:
Expand|Select|Wrap|Line Numbers
  1. CREATE INDEX ON table(country, state, city);
May 20 '13 #2
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.
May 20 '13 #3
983 Expert 512MB

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
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?

May 20 '13 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by jonny | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.