php - 数据库-表设计与查询

Question

我正在开发一个 php 程序，用户需要从下拉列表中选择技能(一项或多项)然后我将结果保存在数组中:用户技能[]然后每个项目也有一个所需技能的列表，我将把它保存在另一个数组中:proj_skills[]

问题是:将这些信息保存到数据库中的最佳方法是什么我应该为项目技能和用户技能创建 2 个新表吗(列:proj_id 和 skill)连续的每个技能，

比较项目与用户技能并找到匹配技能的最佳方法是什么？select id from user_skills where skill=proj_skill[i] and id IN (select id from user_skills where skill=Proj_skill[i+1].......嵌套选择/循环或(递归)

如何去做，最好的和优化的方法是什么？

希望清楚，提前致谢:)

Answer 1

我不知道这是否是最好或最优化的方法，但这是一种方法。 :)

首先，您需要的表格:(为简单起见，我省略了名称列等)

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;

如果您想显示所有至少具有编号为 1、3 和 5 的技能的用户:

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

所以这里我们先用where子句过滤掉所有不相关的技能，然后按用户分组。之后，COUNT(*) 告诉我们每个用户有多少匹配技能。 having 子句仅显示具有 3 种匹配技能的用户，这意味着这些用户至少具有我们正在寻找的技能。

如果您想显示所有具有技能 1、3 和 5 但没有其他技能的用户:

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;

此处第一个子查询查找每个用户的总技能数，第二个子查询查找每个用户的匹配技能数。如果两者都是 3，则用户完全具备我们正在寻找的技能。

如果你想找到所有“兼容”的用户和项目，也就是说，所有(项目，用户) - 用户至少拥有项目所需的所有技能的对:

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;

在这里，第一个子查询找出每个项目所需的技能数量。第二个子查询找出每个(项目、用户)对有多少共同技能。它通过在技能上加入 project_skills 和 user_skills 然后按项目和用户分组来实现。之后，COUNT(*) 告诉每个项目和用户有多少共同技能。最后，我们加入两个子查询并仅显示 (project, user) - 公共(public)技能数量等于项目所需技能数量的对。