Database Table repeat of data in rows

Question

I'm want to compare two tables to see if an employee has a high enough "proficiency level (basic, intermediate, advanced)" in the correct "competencies" required for a job role. Each job role will have 10 competencies but I don't think a table with the following

Columns:

jobroleID, competence1, proficiency1, competence2, proficiency2....competence10, proficiency10 is right but my alternative below also seems wrong since it shows 10 rows repeating the column jobroleID.

table 1 job role requirements

jobroleID, JRCompetence, JRProficiencyLevel

001        205             intermediate
001        207             basic
001        301             advanced
etc
002        

table 2 employee current capability

EmployeeID, EmployeeCompetence, EmployeeProficiencyLevel
E1234       205                 intermediate
E1234       207                 basic
E1234       555                 basic
etc

I appreciate any advice on this.

Answer 1

I think your basic design is fine. Repeating rows is a natural consequence when you normalize a database so that each table only holds information about an entity and you model a many-to-many relationship such as employees and competencies/levels, and jobs and competencies w/ proficiency levels. This design makes it easy to add new requirements - you only have to add new rows in the job requirements table.

Your alternative design would require you to add new columns whenever you need to add new skills, and modify all queries that depend on the table - clearly this is not ideal.

I would however change the design so that the proficiency levels are stored in a separate table, which would make ordinal comparisons easier (so that 1=basic, 2=intermediate, 3=advanced).

A query to find which employees have the skills needed for a particular job could then look like:

-- list emps who has can do job 001:
SELECT EmployeeID
FROM employee_current_capability ecc1
WHERE NOT EXISTS (
    SELECT *
    FROM job_role_requirements jrr
    WHERE jrr.jobroleID = 001
    AND NOT EXISTS (
       SELECT *
        FROM employee_current_capability ecc2
        WHERE ecc1.EmployeeID = ecc2.EmployeeID
        AND ecc2.EmployeeCompetence = jrr.JRCompetence
        AND ecc2.EmployeeProficiencyLevel >= jrr.JRProficiencyLevel
       )
    )
GROUP BY ecc1.EmployeeID;

See this SQL Fiddle for some examples.