Re: How to search?

MinZero wrote:

> Sorry to be too abstract. I didn't want to bore others with
> unnecessary details.
>
> > Okee-dokee, what are the tables, exactly? What's your data model
> > you're dealing with?
>
> The tables of significance are as follows: Customer, Products, Sales,
> Sales_Details

Fair enough.

> > Okee-dokee, what data exactly?
>
> A simple answer would be: everything. There is only one search feature
> on my site. If you search for 'Geo', all the customers with the
> name 'George' and all products with 'Tegeo' should show up
> (That I want to distinguish using proper icons!).

Is there a reason you cannot expand your site to include multiple search features? Are you even providing the option to the user to, for example, not show the results from the Customers table?

> The same should of
> course apply to phone numbers, address and so forth.

Are you sure? It seems that you would only be searching on text-based fields (description, name, street, etc). I get the impression that it wouldn't be reasonable to allow the user to enter a date, for example, or a simple boolean value.

We had a similar problem to overcome with our application (not SQL, but that's irrelevant). What we had to do was actually separate the information stored, so that we could effectively search it. Essentially, you build your own index to the information, then you can index into that.

We have something like (schema names changed to protect the innocent):

	Product -> [ProductID, ProductAvailable, ManufacturerID]
	ProductData -> [FieldID, FieldName, FieldType, FieldDescription]
	ProductIndex -> [ProductID, FieldID, FieldValue].

The ProductIndex table only contains those fields that we want to index. Any others are included in the ProductTable.

> > There are a variety of search engines, programs, models, etc. that
> > might suit you. That's about all I can say given the tiny bit of
> > information you've provided.
>
> Well. I am using PostgreSQL. I want to have multiple companies signed
> up with my system, and store all their data on a single database.
> Moreover, I want to make sure one user can only search records within
> it's company, and not the whole company.

I'll assume you meant "not every company".

> > As much as I hate to say it, and I don't mean to be rude, but I think
> > before you can retrieve an answer that you like, you might have to be
> > more specific in describing your situation.
>
> I appreciate your comments, and would be more than glad to provide more
> details if needed.
>
> > On a happier note, maybe googling "search engine algorithm" might help.
> > Also search the newsgroups. You don't have to reinvent the wheel.
>
> Reinventing the wheel, I cannot afford (Timewise). I seek magic;
> nonetheless I am willing to go to the extreme if that's what it
> takes.

I get the impression that you may need to adjust your user requirements before an elegant and workable solution can be provided.

--