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?
first of all, what you're describing is not normalization
second, going to all that trouble and complexity to save space is, in my opinion, not worth it
in any case, it's not clear what you're asking
what do you mean by efficient sorting?