473,320 Members | 1,732 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 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 1924
Oralloy
985 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);
Cheers,
Oralloy
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
Oralloy
985 Expert 512MB
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
May 20 '13 #4

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

Similar topics

1
by: jonny | last post by:
Where can I find a dbase for index.PHP Country|State|Province|CITY bjve@bjve.com
0
by: Tim Marshall | last post by:
HI folks, I'm doing an app that requires the user to be able to choose a country, then a state/province/territory, then a city and possibly somewhere in-between, some kind of region, perhaps...
2
by: Kevin | last post by:
Hi all, Does anyone know of a script or package that will allow me to calculate the localtime given a country code (and optional state/province for US/Canada)? It should factor in daylight...
5
by: jakas | last post by:
Hi In my php page , there is a user registration form. Here the user has to select the country, state, city from the drop down box. How this can be handled in php? If a country is selected in a...
11
by: pinocchio123 | last post by:
hi friends... in my php page 3 dropdownlist boxes are there.. ie country,state,city.. if country is selected, in onchange function.. corresponding states has to be populated in state...
7
by: tokcy | last post by:
Hi everyone, I need the world database of country state and city. Actually i have three drop down option in my project in 1st drop down country name should come from database and 2nd drop down...
1
by: sahilansari | last post by:
I have a table in MYSQL database... With Country & it's corresponding States. Say i select US from the dropdown option it should automatically show all the states in US from a database file. ...
2
by: dinesh1985singh | last post by:
How do I get the country, state, city, zip code and timezone in PHP from the IP address? I searched it out on google and got lots of link but mostally links I got were paid one, I am searching for...
1
by: parimalareddy | last post by:
Hi In my php page , there is a user registration form. Here the user has to select the country, state, city from the drop down box. How this can be handled in php? If a country is selected in a...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

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.