How to build this table optimally, skills per user based on another table

Question

I have a question which I have "writers" block on. I am building a database for a web-app in Mysql. I have two tables, we'll call them users and jobs. I want to have another table , lets call it skills. Now what I'm wanting is a way to link users to skills they have, and another way to link jobs to those same set of skills. The only way I can think to do this while keeping everything normalized is to basically have an entire list of jobs on each user AND on each job. Of course this is terrible and isn't acceptable to me, not to mention add skills or removing skills in the future would be a nightmare.

So how would I go about doing this? The only thing I must have is a users table (key ID UserID), Jobs table (Key ID JobId) and a skills table (Key Id skillID), I cannot seem to come up with a solution adding tables\rows\or columns and I'm definitely seeking an answer from someone more knowledgeable.

Answer 1

Full answer, expanding on the reply by @shmosel .

Have a table of users and skills, and another table of jobs and skills.

You can then CROSS JOIN the users and jobs to get every combination (narrow it down in the WHERE clause if required), and LEFT OUTER JOIN the jobs / skills table to the jobs. This gives you every combination of jobs and users with the skills required for that job. The LEFT OUTER JOIN the users / skills table to the users table AND to the jobs / skills table.

GROUP BY the user and job, and count the distinct skills on the jobs / skills table, and count the distinct skills on the users / skills table.

Something like this:-

SELECT u.fname,
        j.jobname,
        COUNT(DISTINCT js.skillid),
        COUNT(DISTINCT us.skillid),
        (COUNT(DISTINCT us.skillid) / COUNT(DISTINCT js.skillid)) * 100 AS match_percent
FROM users u
CROSS JOIN jobs j
LEFT OUTER JOIN jobs_skills js  ON j.jobid = js.jobid
LEFT OUTER JOIN users_skills us ON u.userid = us.userid AND js.skillid = us.skillid
GROUP BY u.fname,
        j.jobname

SQL fiddle for it here:-

http://www.sqlfiddle.com/#!9/3d30d/1

Answer 2

You need two additional mapping tables: user_skills would have UserID and SkillID, using their unique combination as a (primary) key. Similarly, job_skills would JobID and SkillID, again with a combined unique key. With that schema in place, any data you need is a simple query away.