1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Rep Power

    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?
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Toronto Canada
    Rep Power
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo