matching keywords against keywords

[sagey]

I am just beginning to write an application that matches developers skills against a project's skills need.

basically developers would enter information about themselves. namely a list of their skills (and maybe the number of years experience)

And a project owner would add a project with details of the types of skills needed for the project (ie. sql server, c++).

my initial thought would obviously to have a developer and project table and for both of these tables to have a skills field which would have a list of the skills in.

i would want both the develoer and the project owner to be able to say. "get me a list of the suitable developers for this project" and likewise the developer to say "get me a list of the projects for which i am suitable".

My problem is i'm not sure how i would write that query.

for s developer to get his i guess the query would be something like

select *
from tbl_project
where skills like '%c++%' or skills like '%vb6%'

etc etc, for however many skills a developer has entered.
(i guess i could use OPENXML () and build an xml string of skills to pass to the query)
This might work. but i don't think it would be very good performance wise, especially if there were thousands of projects (or thousands of developers for the project owner version of that query)

I would be really grateful if someone could give me some guidance on this. Should i design the db a different way? is there a better way to do what is essentially a search of many to many attributes?

Also the ideal for me would be to be able to rank/order the result by suitablility so for a project that required sql server and c++, a developer with both sql server and c++ skills would come above a developer with just sql server skills.

just realised i haven't mentioned the tech i would be using. sql server 2k back end and asp.net

[sagey]

Just did some googling and realised that using ful text searching in sql 2k is probably my best bet. so i'll start reading up on that

If anyone thinks there is a better way of doing what i want, without using full text searchign. please let me know.

[kaffenils]

First of all you should use a lookup table for valid skills, and each skill should be stored in a separate record in two new tables PROJECT_SKILLS(PROJECT_ID, SKILL_ID) and DEVELOPER_SKILLS(DEVELOPER_ID,SKILL_ID). Users should not be allowed to type whatever they want in the skill list. That will make it (almost) impossible to compare. One user may type 'Visual Basic' and another 'VB'. See?

The query that lists suitable projects and developers depend on how you want to match the skills. Do you only want to list developers for a project that has 100% skill match or do you also want to list developers with any of the project skills?

[sagey]

thanks for the reply kaffenils

i did think about having a lookup table for skills.
my reason for not going down that route was
a) thinking that list would have to be pretty huge to be complete. think of all the differen it skills/languages there are.
b) what happens if a skills isn't in that list?
i agree that there could be an issue with people writing synonyms of skills. i'm not sure how i would get round that if i went down the full text search route.

on your second paragraph, i guess it would be best to return all developers with any matching skills, but rank them so the developer with all skills comes top and the developer with only one matching skill comes last.

[kaffenils]

thanks for the reply kaffenils

i did think about having a lookup table for skills.
my reason for not going down that route was
a) thinking that list would have to be pretty huge to be complete. think of all the differen it skills/languages there are.
b) what happens if a skills isn't in that list?
i agree that there could be an issue with people writing synonyms of skills. i'm not sure how i would get round that if i went down the full text search route.

on your second paragraph, i guess it would be best to return all developers with any matching skills, but rank them so the developer with all skills comes top and the developer with only one matching skill comes last.

If the skill is not in the table, simply add it.

I think you will get into serious problems if you let users type whatever they want and then depend on SQL Server's fulltext search engine to find matches.

[sagey]

ok, so if i did go the lookup route. how would i write a query that says

for project x which has y skills against it
return all developers with matching skills order by the most relevant developer.

and

for developer x which has y skills against him
return all projects with matching skills ordered by most relevant project.

With the db design you have mentioend i'm not sure how i would go about that?

[si_the_geek]

For that I would create a temporary table to return the results. Here's one possible way of doing it:

• Create a temporary table with 2 columns: DeveloperID, and SkillCount.
• Insert DeveloperID from the developer table, and set SkillCount to 0.
• For each skill listed, update the SkillCount value (+1) if it is in their profile.
• To get the results in order, simply Join this table into your SQL, and order by SkillCount Desc.

[kaffenils]

ok, so if i did go the lookup route. how would i write a query that says

for project x which has y skills against it
return all developers with matching skills order by the most relevant developer.

and

for developer x which has y skills against him
return all projects with matching skills ordered by most relevant project.

With the db design you have mentioend i'm not sure how i would go about that?

I have a query that will give you exactly what you want (projects, total number of skills per project, developer with matching skills and number of matching skills), but it's on my pc at work. I will post it tomorrow.

[sagey]

Cheers kaffenils that sounds great. i'll look forward to your post.

[kaffenils]

Cheers kaffenils that sounds great. i'll look forward to your post.

Here is the query that will give you the information you need. You can filter by project_id or developer_id or just get a complete list. NB: I haven't testet it a large amounts of data, so I'm not sure how it willperform if there of houndreds of thousands of records.

select p.project_id,p.project_no,
(select count(*) from project_skills where project_id=p.project_id) as project_skill_count,
d.developer_id,d.developer_name,count(d.developer_id) as developer_skill_count
from projects p inner join project_skills ps on ps.project_id=p.project_id
inner join skills s on ps.skill_id=s.skill_id
inner join developer_skills ds on ps.skill_id=ds.skill_id 
inner join developers d on d.developer_id=ds.developer_id
group by p.project_id,p.project_no,d.developer_id,d.developer_name
order by p.project_id, count(d.developer_id) desc,d.developer_name

And if you need the table definitions I have used:

CREATE TABLE [dbo].[Developer_Skills] (
	[Developer_Id] [int] NOT NULL ,
	[Skill_Id] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Developers] (
	[Developer_Id] [int] IDENTITY (1, 1) NOT NULL ,
	[Developer_Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Project_Skills] (
	[Project_Id] [int] NOT NULL ,
	[Skill_Id] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Projects] (
	[Project_Id] [int] IDENTITY (1, 1) NOT NULL ,
	[Project_No] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Skills] (
	[Skill_Id] [int] IDENTITY (1, 1) NOT NULL ,
	[Skill_Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Developer_Skills] WITH NOCHECK ADD 
	CONSTRAINT [PK_Developer_Skills] PRIMARY KEY  CLUSTERED 
	(
		[Developer_Id],
		[Skill_Id]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Developers] WITH NOCHECK ADD 
	CONSTRAINT [PK_Developers] PRIMARY KEY  CLUSTERED 
	(
		[Developer_Id]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Project_Skills] WITH NOCHECK ADD 
	CONSTRAINT [PK_Project_Skills] PRIMARY KEY  CLUSTERED 
	(
		[Project_Id],
		[Skill_Id]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Projects] WITH NOCHECK ADD 
	CONSTRAINT [PK_Projects] PRIMARY KEY  CLUSTERED 
	(
		[Project_Id]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Skills] WITH NOCHECK ADD 
	CONSTRAINT [PK_Skills] PRIMARY KEY  CLUSTERED 
	(
		[Skill_Id]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Developer_Skills] ADD 
	CONSTRAINT [FK_Developer_Skills_Developers] FOREIGN KEY 
	(
		[Developer_Id]
	) REFERENCES [dbo].[Developers] (
		[Developer_Id]
	) NOT FOR REPLICATION ,
	CONSTRAINT [FK_Developer_Skills_Skills] FOREIGN KEY 
	(
		[Skill_Id]
	) REFERENCES [dbo].[Skills] (
		[Skill_Id]
	) NOT FOR REPLICATION 
GO

ALTER TABLE [dbo].[Project_Skills] ADD 
	CONSTRAINT [FK_Project_Skills_Projects] FOREIGN KEY 
	(
		[Project_Id]
	) REFERENCES [dbo].[Projects] (
		[Project_Id]
	) NOT FOR REPLICATION ,
	CONSTRAINT [FK_Project_Skills_Skills] FOREIGN KEY 
	(
		[Skill_Id]
	) REFERENCES [dbo].[Skills] (
		[Skill_Id]
	) NOT FOR REPLICATION 
GO

[sagey]

wow! thanks kaffenils.

I haven't had a chance to check this out properly but on first impressions it looks like just what i need thanks a lot.

out of interest what sort of project did you use this code for? i'm surprised that you have dealt with my exact problem.

[kaffenils]

wow! thanks kaffenils.

I haven't had a chance to check this out properly but on first impressions it looks like just what i need thanks a lot.

out of interest what sort of project did you use this code for? i'm surprised that you have dealt with my exact problem.

I created it just for you. Thank me by rating my post