Question: 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 Afghanistan 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?

Answer: 
**This is a typical pitfall of normalization in databases: normalizing for the sake of space only.**

Space is cheap.

Think in terms of functional dependencies: the tuple (Country, State, City) is supposed to be the Functional dependency but you don't have any information that depends on it, nor of its components (at least you have not told us). Alternatively you could have certain functional dependencies such State->Country, or City -> Coutry,State (but you already know that this is not the case for a global database).

You could equally have it as a single attribute "Country;State;City" and it would not have affected your design (from a functional dependency point of view; it would have from a searching data point of view).

In other words, you would want to normalize this table if you have any information specific to the tuple (Country, State, City), or you have functional dependencies you want to enforce within the tuple itself.

if you don't, then don't normalize it just for the sake of space. Space has never been the main motivation behind normalization (update/insert/delete anomalies are the main reason).

Put it this way. Would you normalize the first name and last name of a person just for the sake of saving space? 

If you still want to do it, then don't worry about how indexes sort the data. That should not be your worry. You can create an tree-based index --the default-- on (Country, State, City) and you can then search for a given country, or a country state, or a country, state, city (you can always use a prefix in a tree-based index). you can create as many indexes as you want on a table with any combination of attributes. But this will take space, and will slow down insertions, but I suspect you won't have many in that table anyways.

**My suggestion, don't normalize this data.**

--dmg
In most (next to all) situations the normalized form is the best form. In your example, I would suggest you make a `SELECT` on the combination of city, state, country and see if the new value already exist and if not create the lines before adding the user. That is slightly more costly on insertion time (because you will have two queries) but will reduce your disk usage and selection time.

You should place an index on the city, state and country tables, though.

Be aware that this way you will have duplicate entries for the same place (because `Munich, Bavaria, Germany` is the same as `München, Bayern, Deutschland`, but you will have no chance to know.