I'm beginning my foray into fulltext search for MSSQL2005. This is a new, in-dev database, so I can monkey around with it.
Let's say I have a "Form" table that has a one-to-many relationship to two other tables, "Answer" and "Comment." I want the user to be able to enter one search term and have it peg all three of those tables via fulltext search. What's the best way? It appears as though I can create an indexed view to speed things along.
If I do use an indexed view (is this the best route anyway?), how do I handle the one-to-many relationships? Let's assume all I really want to figure out is the ID of the "Form" table that would contain any search results found in the child Answer/Comment tables, so I can show the entire form that contains the located entries. An inner join on the view would result in multiple "master" form rows being returned - doesn't sound great for performance. How about concatenating all child answer/comment rows into one "column" for the view? So instead of....
Form 1, Answer A, Comment A
Form 1, Answer B, Comment A
Form 1, Answer A, Comment B
Form 1, Answer B, Comment B
Form 1, Answer A Answer B, Comment A Comment B
Remember all I care about is the ID of that master "form" row.
This seems logical to me, but I'm not sure the best practices around fulltext search yet, so just curious if I'm approaching it correctly.
UPDATE: Looks like indexed views are pretty strict; no left joins, no subqueries, so I might go down the path of a summary table populated by triggers if it isn't going to be too unwieldy.