Extending SQL Server full-text index to search through foreign keys

Question

I know that a SQL Server full text index can not index more than one table. But, I have relationships in tables that I would like to implement full text indexes on.

Take the 3 tables below...

Vehicle
Veh_ID - int (Primary Key)
FK_Atr_VehicleColor - int
Veh_Make - nvarchar(20)
Veh_Model - nvarchar(50)
Veh_LicensePlate - nvarchar(10)

Attributes
Atr_ID - int (Primary Key)
FK_Aty_ID - int
Atr_Name - nvarchar(50)

AttributeTypes
Aty_ID - int (Primary key)
Aty_Name - nvarchar(50)

The Attributes and AttributeTypes tables hold values that can be used in drop down lists throughout the application being built. For example, Attribute Type of "Vehicle Color" with Attributes of "Black", "Blue", "Red", etc...

Ok, so the problem comes when a user is trying to search for a "Blue Ford Mustang". So what is the best solution considering that tables like Vehicle will get rather large?

Do I create another field in the "Vehicle" table that is "Veh Color" that holds the text value of what is selected in the drop down in addition to "FK Atr VehicleColor"?

Or, do I drop "FK Atr VehicleColor" altogether and add "Veh Color"? I can use text value of "Veh Color" to match against "Atr Name" when the drop down is populated in an update form. With this approach I will have to handle if Attributes are dropped from the database.

-- Note: could not use underscore outside of code view as everything between two underscores is italicized.

Answer 1

As I understand it (I've used SQL Server a lot but never full-text indexing) SQL Server 2005 allows you to create full text indexes against a view. So you could create a view on

SELECT 
  Vehicle.VehID, ..., Color.Atr_Name AS ColorName 
FROM
  Vehicle 
LEFT OUTER JOIN Attributes AS Color ON (Vehicle.FK_Atr_VehicleColor = Attributes.Atr_Id)

and then create your full-text index across this view, including 'ColorName' in the index.

Answer 2

I believe it's a common practice to have separate denormalized table specifically for full-text indexing. This table is then updated by triggers or, as it was in our case, by SQL Server's scheduled task.

This was SQL Server 2000. In SQL Server you can have an indexed view with full-text index: http://msdn.microsoft.com/en-us/library/ms187317.aspx. But note that there are many restrictions on indexed views; for instance, you can't index a view that uses OUTER join.

Answer 3

You can create a view that pulls in whatever data you need, then apply the full-text index to the view. The view needs to be created with the 'WITH SCHEMABINDING' option, and needs to have a UNIQUE index.

CREATE VIEW VehicleSearch
WITH SCHEMABINDING
AS
SELECT
  v.Veh_ID,
  v.Veh_Make,
  v.Veh_Model,
  v.Veh_LicensePlate,
  a.Atr_Name as Veh_Color
FROM
  Vehicle v
INNER JOIN
  Attributes a on a.Atr_ID = v.FK_Atr_VehicleColor
GO

CREATE UNIQUE CLUSTERED INDEX IX_VehicleSearch_Veh_ID ON VehicleSearch (
  Veh_ID  ASC
) ON [PRIMARY]
GO

CREATE FULLTEXT INDEX ON VehicleSearch (
  Veh_Make  LANGUAGE [English],
  Veh_Model LANGUAGE [English],
  Veh_Color LANGUAGE [English]
)
KEY INDEX IX_VehicleSearch_Veh_ID  ON [YourFullTextCatalog]
WITH CHANGE_TRACKING AUTO
GO