Question on table design

Question

I'm offering a search option for my users. They can search on city name. The problem is that my city names I have stored are things like "Saint Louis". But I want to find Saint Louis even if the user types in "St. Louis" or "St Louis". Any suggestions on how I could create a lookup table to take that into account somehow?

Answer 1

Create two tables.

One contains everything about a city.

One contains a bunch of names for cities, and a foreign key association those naes with the id of the the first table. So you have a one to many relationship between city and city_names.

Now the only problem is distinguishing the one name, for each city, that is the preferred name. We can do that a couple of ways: 1) the first table could have a fk to the second table, that holds to id of the preferred name. This creates a circular dependency, though. So better, 2) just add a boolean/bit column to the second table, is_preffered.

create table city (id not null primary key, other columns ) ;

create table city_name (
 id not null primary key, 
 city_id int references city(id), 
 name varchar(80),
 is_preferred bool  
) ;

Then to get all names, with the preferred name first:

   select name from city_names where city_id = ? 
   order by is_preffered desc, name;

This has an additional advantage: if you don't cover every city and town, you can use the second table to map towns/villages/counties you don't cover to the major cities you do:

 insert into city_name(city_id, name) values
 ( $id-for-New-York-City, 'New York'),
 ( $id-for-New-York-City, 'Manhattan'),
 ( $id-for-New-York-City, 'Big Apple'),
 ( $id-for-New-York-City, 'Brooklyn');