Geek Answers Handbook

Question about table design

I offer a search option for my users. They can search by city name. The problem is that the names of my cities that I have saved are things like St. Louis. But I want to find St. Louis, even if the user enters “St. Louis” or “St. Louis”. Any suggestions on how I can create a lookup table to take this into account somehow?

+7
full-text-search database-design
source share
8 answers

Create two tables.

One contains everything about the city.

One contains a bunch of names for cities and an association of foreign keys that have names with the identifier of the first table. Thus, you have a relationship to each other between cities and cities.

Now the only problem is to distinguish one name for each city, which is the preferred name. We can do this in several ways: 1) the first table can have fk in the second table, which contains the identifier of the preferred name. However, this creates a cyclical dependency. So better, 2) just add the 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 the names with the preferred name:

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

This has an additional advantage: if you do not cover every city and city, you can use the second table to compare cities / villages / counties that you do not cover to the main cities that 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');
+5
source share

What would I do, create a table of abbreviated lines that will display any ambiguous word for one consistent spelling that you will use in your main table. You may include common spelling mistakes and typos.

Before searching for a user request, convert all the words into a regular form using this table.

So, in your case, in the shorthand-to-normal table, we will have

  ______________ | short|normal | |______|_______| |St |Saint | |St. |Saint |
+3
source share

You might want to explore a more full-featured full-text search engine, such as Apache Lucene / Solr or Sphinx - which can support this type of string display natively.

+1
source share

I see several possible ways to deal with this. One of them is the soundex search algorithm, which matches the similarity of English strings. In addition, it is supported in some databases, such as PostgreSQL .

Another approach may be to offer their users automatic full functionality when they type in a few sentences. Thus, users intuitively select the desired city name.

+1
source share

As a general approach, you can normalize elements both during insertion and when searching.

Normalization rules can be:

 Saint => St St. => St

and etc.

The corresponding names must match.

+1
source share

IMHO, I would leave the database alone and instead have a list of abbreviated cities in your application. Easier, cleaner and does not require much.

0
source share

I like the option in the first answer.

Another thought would be to have tag columns for this city that update the users cooler.

i.e.

The official name is New York.

Tags for this city would be numerical (Manhattan, New York, New York, city, big apple ..) etc but you don’t want all this to be undesirable in your main city table or to create related child tables and to perform joins . So just drag it into the columns and find it based on the search query, but then return its name if it is found.

0
source share

You can use the built-in SQL FTS properties for thesaurus entries. This allows you to call up a custom word map inside a full-text search. This way you can store everything inside the FTS, rather than mixing FTS and other requests.

I don't know which version of SQL you are using as its difference between 2005/8, so there is a good step-by-step guide for 2005/8 here http://arcanecode.com/2008/05/28/creating-custom-thesaurus-entries-in -sql-server-2005-and-2008-full-text-search /

0
source share

More articles:


All Articles