Database - tables design and query

Question

i'm working on a php program, the user need to select skills(one or more) from a drop-down list then i will save the result in array: user_skills[] then each project has a list of required skills too, i'll save it in another array: proj_skills[]

the question is: what is the best way to save these info into the database should i create 2 new tables one for the projects skills and user skills (columns: proj_id and skill) each skill in a row,

and what is the best way to compare project with users skills and find match skills ? select id from user_skills where skill=proj_skill[i] and id IN (select id from user_skills where skill=Proj_skill[i+1]....... nested select/loop or (recursion)

how to do it, whats the best and optimized way to do it ?

hope it's clear, thanks in advance :)

Aleksi Torhamo · Accepted Answer · 2012-02-04 08:40:12Z

I do not know if this is the best or most optimized way to do it, but here's one way to do it. :)

First, the tables you'll need: (I've left out name columns etc. for simplicity)

CREATE TABLE skills (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (id)
) ENGINE=InnoDB;

CREATE TABLE users (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (id)
) ENGINE=InnoDB;

CREATE TABLE projects (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (id)
) ENGINE=InnoDB;

CREATE TABLE user_skills (
    user_id INT UNSIGNED NOT NULL,
    skill_id INT UNSIGNED NOT NULL,
    PRIMARY KEY (user_id, skill_id),
    FOREIGN KEY (user_id) REFERENCES users (id),
    FOREIGN KEY (skill_id) REFERENCES skills (id)
) ENGINE=InnoDB;

CREATE TABLE project_skills (
    project_id INT UNSIGNED NOT NULL,
    skill_id INT UNSIGNED NOT NULL,
    PRIMARY KEY (project_id, skill_id),
    FOREIGN KEY (project_id) REFERENCES projects (id),
    FOREIGN KEY (skill_id) REFERENCES skills (id)
) ENGINE=InnoDB;

If you want to show all users that have at least the skills numbered 1, 3 and 5:

SELECT user_id
FROM user_skills
WHERE skill_id IN (1, 3, 5)
GROUP BY user_id
HAVING COUNT(*) = 3;

So here we first filter out all the irrelevant skills with the where clause and then group by user. After that, COUNT(*) tells us how many matching skills each user had. The having clause then only shows users with 3 matching skills, which means those users had at least the skills we were looking for.

If you want to show all users that have skills 1, 3 and 5 but no other skills:

SELECT s1.user_id
FROM (
    SELECT user_id, COUNT(*) count
    FROM user_skills
    GROUP BY user_id
) s1
INNER JOIN (
    SELECT user_id, COUNT(*) count
    FROM user_skills
    WHERE skill_id IN (1, 3, 5)
    GROUP BY user_id
) s2 ON (s1.user_id = s2.user_id)
WHERE s1.count = 3 AND s2.count = 3;

Here the first subquery finds the total skill count for each user and the second subquery finds the number of matching skills for each user. If both are 3, the user has exactly the skills we are looking for.

If you want to find all "compatible" users and projects, that is, all (project, user) -pairs where the user has at least all the skills the project needs:

SELECT s2.project_id, s2.user_id
FROM (
    SELECT project_id, COUNT(*) count
    FROM project_skills
    GROUP BY project_id
) s1
INNER JOIN (
    SELECT project_skills.project_id, user_skills.user_id, COUNT(*) count
    FROM project_skills
    INNER JOIN user_skills ON (user_skills.skill_id = project_skills.skill_id)
    GROUP BY project_skills.project_id, user_skills.user_id
) s2 ON (s1.project_id = s2.project_id)
WHERE s2.count = s1.count;

Here, the first subquery finds out the number of skills needed for each project. The second subquery finds out how many common skills there are for each (project, user) -pair. It does this by joining project_skills and user_skills on skill and then grouping by project and user. After that, COUNT(*) tells how many common skills each project and user has. Finally, we join the two subqueries and only show (project, user) -pairs where the number of common skills equals the number of skills the project needs.

gangreen · Accepted Answer · 2012-02-04 06:56:18Z

If a user can be associated with multiple projects, and a project can be associated with multiple users, it sounds like it would be best to store the skills for users and projects in separate tables.

After setting up both tables, I would create a third table to hold all possible skills (I'm assuming that there are no skills that can only be associated with a user or only with a project). So now you will have the following tables, Users, Projects, UserSkills, and ProjectSkills.

Once those are set up, and assuming you can pass the UserID and ProjectID into the query, I would just join the user skills with the project skills on the SkillID. E.g.,

SELECT Q1.UserID,
       Q1.Skill
FROM   (SELECT Users.UserID,
               Skills1.Skill,
               Skills1.SkillID
        FROM   Users
               INNER JOIN UserSkills
                 ON Users.UserID = UserSkills.UserID
               INNER JOIN Skills AS Skills1
                 ON UserSkills.SkillID = Skills1.SkillID
        WHERE  Users.UserID = 123) AS Q1
       INNER JOIN (SELECT Projects.ProjectID,
                          Skills2.Skill,
                          Skills2.SkillID
                   FROM   Projects
                          INNER JOIN ProjectSkills
                            ON Projects.ProjectID = ProjectSkills.ProjectID
                          INNER JOIN Skills AS Skills2
                            ON Project.SkillID = Skills2.SkillID
                   WHERE  ProjectID = 456) AS Q2
         ON Q1.SkillID = Q2.SkillID;

In general, you will want to avoid iterating when using SQL. Your best performance bet is to ask for exactly what you want only once. Also, if you iterate the way you wrote above, you are assuming that all the IDs will be in sequence, but it's possible (and likely) that the skills will not be. So, the two could have skills 1 and 3 in common, but not 2. Would you stop your loop after receiving nothing in common on skill 2? You have to think in sets. Also, be careful with the IN clause. If you you try to test to see if something is "NOT IN" a result set that contains a null, you will not receive any results even if you the user and project have skills in common. Here's an article on the topic http://bharatmane.com/blog/?p=52

No'am Newman · Accepted Answer · 2012-02-04 06:48:21Z

I'm not a php coder so some of your question passes me by, but in terms of database design, you need three tables: people, skills and skillsperperson. The latter table would be a 'link table', with at least two fields which serve as a joint primary key: person and skill. You could add to this table the degree to which the person possesses the skill.

If you have a table for projects and another link table for all the skills needed for that project, then in order to find people who possess the necessary skills for that project, you would need to write something like this:

select people.name, skills.name
from people inner join skillsperperson on people.id = skillsperperson.person
inner join skills on skillsperperson.skill = skills.id
inner join skillsperproject on skills.id = skillsperproject.skill
where skillsperproject = :p1;

(:p1 is a parameter which you pass to the query; its value will be the id of the given project).

Collectives™ on Stack Overflow

Database - tables design and query

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related