CODEWITHSUNDEEP
sql
user20358
user20358

Reputation: 14736

User to location mapping with country state and city in the same table

I have a user table that has among others the fields CityId, StateId, CountryId. I was wondering if it was a good idea to store them[City, State, Country] in separate tables and put their respective ids in the User table or put all the three entities in one table.

While the former is conventional, I am concerned about the extra tables to join and so would want to store all these three different location types in one table like so

RowId - unique row id
LocationType - 1 for City, 2 for state, etc
ActualLocation - Can be a city name if the locationType is 1 and so on..

RowId     LocationType   ActualLocation
1          1                 Waltham
2          1                 Yokohama
3          2                 Delaware
4          2                 Wyoming 
5          3                 US
6          3                 Japan

the problem is I am only able to get the city name for all three fields using a join like this

select L.ActualLocation as CityName,  
       L.ActualLocation as StateName,
       L.ActualLocation as CountryName      
        from UserTable U,
        AllLocations L
    WHERE 
        (L.ID = U.City and L.LocationType= 1)
    AND
        (L.ID = U.State and L.LocationType = 2)

Upvotes: 0

Views: 2018

Answers (2)

Captain
Captain

Reputation: 2218

You actually need to select from your location table three times - so you will still have the joins:

select L1.ActualLocation as CityName,  
   L2.ActualLocation as StateName,
   L3.ActualLocation as CountryName      
    from UserTable U,
    AllLocations L1,
    AllLocations L2,
    AllLocations L3
WHERE 
    (L1.ID = U.City and L1.LocationType= 1)
AND
    (L2.ID = U.State and L2.LocationType = 2)
AND
    (L3.ID = U.Country and L3.LocationType = 3)

HOWEVER Depending what you want to do with this, you might want to think about the model... You probably want a separate table that would contain the location "Springfield Missouri" and "Springfield Illinois" - depending how "well" you want to manage this data, you would need to manage the states and countries as separate inter-related reference data (see, for example, ISO 3361 part 2). Most likely overkill for you though, and it might be easiest just to store the text of the location with the user - not "pure" modeling, but much simplified for simple needs... just pulling the "word" out into a separate table doesn't really give you much other than complex queries

Upvotes: 1

LeeG
LeeG

Reputation: 728

What worked best for us was to have a country table (totally separate table, which can store other country related information, a state table (ditto), and then the city table with ID's to the other tables.

CREATE TABLE Country (CountryID int, Name varchar(50))
CERATE TABLE State (StateID int, CountryID int, Name varchar(50))
CREATE TABLE City (CityID int, StateID int, Name varchar(50))

This way you can enforce referential integrity using standard database functions and add additional information about each entity without having a bunch of blank columns or 'special' values.

Upvotes: 3

Related Questions