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: 
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.
> 
> 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 )
> 
> 
> 

What you're describing isn't normalization. 

If you *were* trying to normalize your original table, you'd identify a functional dependency, a multi-value dependency, or a join dependency in the original table, and you'd remove that dependency by projection. 

But you're not doing that. You're just picking three closely related columns and replacing repeated text with a repeated ID number. And that's not normalizing.

The only possible dependency, given what little you've told us, is that {city, state->country}. Whether that dependency holds is application-dependent, but it doesn't hold on a global scale. If I recall correctly, as of a few years ago there were duplicates in a some Arabic countries and in some Latin American countries.

If, on the other hand, you want an additional table(s) that you can use to increase data integrity, and that application code can use to present sorted possibilities to the user, that's pretty straightforward. But that has to do with data integrity, not with normalization.

You can store countries, states, and cities like this. I'm not recommending you *do* this *exactly*; it's an example. In practice, you should think hard about using ISO country codes.

* [ISO 3166-1](http://en.wikipedia.org/wiki/ISO_3166-1), "Country" codes
* [ISO 3166-2](http://en.wikipedia.org/wiki/ISO_3166-2), "State" codes

.

```
create table countries (
  country_name varchar(35) not null,
  primary key (country_name)
);

create table states (
  country_name varchar(35) not null,
  state_name varchar(35) not null,
  primary key (country_name, state_name),
  foreign key (country_name) 
    references countries (country_name)
);

create table cities (
  country_name varchar(35) not null,
  state_name varchar(35) not null,
  city_name varchar(35) not null,
  primary key (country_name, state_name, city_name),
  foreign key (country_name, state_name)
    references states (country_name, state_name)
);

```

Set a foreign key reference from your table to the table "cities".