Hello All,
Looking to get to some insight into how I should go about setting up a
Full Text Search with, to put it lightly, a huge set of normalized
data. We would be collecting various sets of resume based fielded data
along the lines skills, education, etc... and the resume itself. The
sets of the data would obviously have a many to one relationships
throughout (many
skills to one candidate) and I'm loooking to see how to efficiently
configure the data to enable efficient, filtered and RANKED results
using FTS. Here are some concepts I've been reviewing / Considering
with FTS:

If I started with normalized data in a mock form as below...(quick mock
of table structures)

Cand (CandID [PK], First, Last, etc...)
Resume (CandID [FK], ResID [PK], ResText, ResDateEntered, ResDateMod,
etc...)
Skills (CandID [FK], SkillID [PK], Skill, Year, Date, etc...)

1) Creation of "Search table" that contains XML typed field conatining
individual XML docs of the entire set of candidate information along
with a primary key using FTS with xQuery...this would work but I would
like to have a ranking scenario which is not available using the
CONTAINS with XML data.

Eg. SearchTable (SearchID [PK], AllCandidateInfoXML)

2) Creation of "Search table" with group fields with one field
containing merged text data (resumes, skills, etc...) and individual
"Group" fields containing all the skills (skill1, skill2, etc...),
other fields for all the education, etc... This could drive the table
size through the roof.

Eg. SearchTable (SearchID [PK], AllCandidateInfoText, AllSkills,
AllEducation, AllWorkHistory, AllRelcationSites, etc...)

3) Using FTS on the "resume table" and building the TSQL query to
search and filter on the joined related tables...would work but worried
about performance across many tables

4) Creation of a view using a similar concept as 2)

The searching concept is the keywords would encompas all data but
specific search fields would filter based only on those fields. Result
set would be the subset of the keyword search based on the filtered
fields but ranked.

Another side Item as well, SQL Server 2005 has a thesaurus function,
does anyone know what the extent of it's "vocabulary" is and is it
expandable?
For Example-
Firstname: David
Lastname: Tester
Skillset: ASP.NET Years: Years: 5
Skillset: SQL 2005 Years: 3
Skillset: Visual Basic.NET Years: 5
Resume: ...Resume content with keywords like Tester and ASP.NET...

I want to be able to search on keyword. In the above example, if I
searched for ASP.NET I would get this resume in the result. If I
searched for VB.NET, I would also like to get this result, even though
the VB.NET is not in the resume or in the fields. In other words, I
would like to have a thesaurus/translation of skillsets and
abbreviation mappings.

Eg. VB.NET = Visual Basic . NET = Visual Basic (just an example)

I would need to build some knowledge base of translations... (anyone
know where I can find a technologies thesaurus?)  Any suggestions on
how to do this for maximum performance?

Again, I'm looking for some emperical feedback from people who have had
similar problems and even "matter-of-fact" that FTS may not be the
end-all, be-all for this type of problem.
Any recommendations of products that might solve these problems easily
as well?

Thanks
Dave
datherton@ssppaammxunity.com
(remove ssppaamm to email me)