Full text search on normalized database

Question

I have a normalized sql server 2005 database. An example of a table that is something like this:

Location

---

LocationID, CustomerID, OrderID

This is abbreviated. However, the normal query syntax simply uses joins to show the location as city state zip and the name of the customer and so on.

I would like to implement full text search on those values. So if LocationID = 43 which is Phoenix AZ I would like the user to be able to search for 'Phoenix' or 'AZ' and return the associated rows. Similarly, if they search for 'Smith Phoenix' they will get all orders for a customer with a name similar to Smith in Phoenix.

My question is, should I use a View or a UDF to build a table that replaces the value 43 with 'Phoenix AZ'? And implement fulltext search from there?

How do I implement fulltext search on a normalized database?

Answer 1

You need to add the full text index on the table that has the string values. Then use CONTAINS or FREETEXT along with your joins.

Also look into CONTAINSTABLE and FREETEXTTABLE.

http://doc.ddart.net/mssql/sql2000/html/acdata/ac_8_qd_15_1m9f.htm

Answer 2

Honestly, for something like this, I'd use Lucene.NET (assuming a .NET front end, or just Lucene for the back end). While you could search on each of those items, I've found that SQL Server full-text search is more of a pain than it's worth.

With Lucene, you create indexes when you add/edit/delete items in the DB, and then search those indexes (each item is a document with fields which you specify).