Search logic and algorithm

Need help figur开发者_开发百科ing this out.

I have two tables

user
----
name
skills (comma separated value of skills;not mapped to next table)

where a users name and his technical skills are stored.

skills
-----
id
language_name

where we store only skills without mapping to anything.

And I have a search form with one textfield where you can input the primary skill :

eg. java javascript html css

Based on the value entered I want to find the best person with the technical skills and also display the person who comes closest to the skills searched.

Any idea how do I go about achieving this. Recommendations for changes in table structures are also welcome.

Note: The best person is the one who has the greatest number of skills in the list.

---

You can either use a normal normalised tag table for the skills and use SQL, or you can store the skills as a single list and use FREETEXT searching on the single column.

user       : id; name
skill      : id; language_name
user_skill : user_id; skill_id

Every time you encounter a new skill, just create a new entry for it in skill, so ".Net", "DotNet", "C#.Net" would appear 3 times.

When searching for the best match for "java javascript html css", break that into a list of 4, so the query looks like this

select top 10 u.name, count(*) matched
from skill s
inner join user_skill us on s.skill_id = us.skill_id
inner join user u on u.user_id = us.user_id
where s.language_name in ('java','javascript','html','css')
group by u.name
order by matched desc

(shows the top 10)

On SQL Server 2005, with the help of FOR XML, you can also get the list of missing skills per each of the best 10 matching users

select top(10)
    u.id,
    u.name,
    count(*) matched,
    stuff(
    (
     select ',' + s.language_name
     from skill s
     where s.language_name in ('java','javascript','html','css')
       and not exists (
        select * from user_skill u2
        where u2.user_id=u.id
          and u2.skill_id=s.skill_id)
     for xml path('')
    ),1,1,'') as missing_list
from skill s
inner join user_skill us on s.skill_id = us.skill_id
inner join [user] u on u.user_id = us.user_id
where s.language_name in ('java','javascript','html','css')
group by u.id, u.name
order by matched desc