> I think the substring idea is a great one.
>
> It took me a little while to understand what you were having me do with
> the substring search table. You're suggesting I take the customer

name,

> and break it up into it's searchable parts, and store each part (with a
> duplicate id number) in the search table. This way a high-speed

indexed

> search can find a subgroup of id's - with the tradeoff of higher
> insert/update times (not a concern for me for this) and increased disk
> usage (also not a concern right now).
>

That is the normal way of doing it in Firebird. Obviously an index can
only help a "containing" query if it can be transformed to a "Starting
with".

When the number of customers gets large, a fetch all query will take
an awful long time to return and there will be some UI design issues
as well. If you restrict it with the where clause, the execution time
of the query will be just as slow because it can't use an index to
narrow down the results, but at least the fetch time is going to be
reasonable.

So the suggestion is to have a generic words table, a customer table,
and a table linking customers and words.

eg

Customer
ID, Name
1, The Firebird Foundation
2, IBPhoenix

Words
ID, Name
1, The
2, Firebird
3, Foundation
4, IBPhoenix

CustomerWords
CustomerID, WordsID
1,1
1,2
1,3
2,4

etc

Obviously, you will want to store words in upper case or something,
and you can use a query similar to this:

select distinct c.ID, c.Name
from Words w
join CustomerWords cw on (w.ID = cw.WordsID)
join Customer c on (cw.CustomerID = c.ID)
where w.Name containing 'F%'

I would also recommend using a first n syntax, if they happen to type
"International" etc as a keyword, they may get a lot of companies,
there is no point showing them all.

This method is not specific to HTML, any interface needs to deal with
this issue. Specifically using html, if you have the option to use
javascript etc, you could have some key combination Ctrl + something
to run the query with the current keywords.

Adam