I'm looking for a little nudge in a new direction on this one. The issue involves creating a web based search form that returns results a user would expect and find useful.

In a nutshell I have three tables "products", "resources" and a table which joins the two. This allows me to associate a resource to several products.

So, for example, the product table has a name and description fields, the resource table has a name, description and content fields (all text fields). If the user searches for the word "wigit", I would want to search the resource name, resource description, product name, product description, and product content. Then return some relevant, consistent results. Sounds simple enough. But my solutions tend to bloat into messiness and I am just not happy with the end results.

I have used MySQL's fulltext indexing with some success. But, it has it's limitations, one being it's not good with single word queries. So, I have created a hybrid method. If the query consists of several words, I use fulltext, if not, I use wild cards. The problem with this is inconsistent (displayed) results, with fulltext I can use the score to sort by relevance, with wild cards of course I cannot (I end up sorting by product or resource name).

Another issue is deciding how to join the tables and how they should be grouped. For example, if "wigit" matched a resource name, I would want to join the products on the resource table (to show which products are associated to that resource).

I added an autocomplete function to the searching that has helped a bit. But, since adding that I have dropped fulltext completely.

Hopefully, I have explained the problem enough. I am mainly looking for ideas or nudges to get me looking in different directions and other options to consider. Maybe ways you have approached this problem. I am open to any feed back.

Thanks for reading and offering any ideas/thoughts you have.