Using a field for Alternative names

[Baxtula]

Hello,

I am designing a database for my school library.

10% of the books in the database are French, and thus have words with accents like Château.

My issue is that I'd like the user to search for book by typing Chateau (as it is too difficult to type in words with accents), however, when a list of results come back, it would appear as Château

My initial solution was to build a field for alternative names to do the search under

For example

TitleID: 1001
Title: Château
Alt_Title: Chateau

However, since only 10% of the books have accents, there would be 90% redundancy, as all the other books would look like this.

TitleID 1002
Title: Catcher in the Rye
Alt_Title: Catcher in the Rye

Is there a simpler solution?

Joel

[AndreRet]

Use the LIKE function when a user searches.

SELECT FROM MyBooks WHERE Title LIKE %Chateau%

[Baxtula]

Brilliant! Thanks!

[AndreRet]

Only a pleasure.

[griswolf]

Let me add an idea. If you are going to use "LIKE" then you need to decide what things are wild cards (all vowels?). There may be a better way:

A really helpful search technique would look for homophone matches as well as more accurate spellings . Soundex is an English-based system which might be helpful. I might suggest a secondary table containing columns `sounds_like` and a primary key into the main table. Multiple word titles, of course would have multiple rows in that table; and the lookup could not be done (easily) entirely in SQL.

[Baxtula]

Great idea! I'll try that out Griswolf!