MySql表可能*非常*很多列 [英] MySql table with potentially *very* many columns

问题描述

一个招聘软件工程师的朋友要我为他创建一个应用。

A friend who is a recruiter for software engineers wants me to create an app for him.

他希望能够根据技能搜索候选人的简历。

He wants to be able to search candidates' CVs based on skills.

您可以想象，可能有数百种甚至数千种技能。

As you can imagine, there are potentially hundreds, possibly thousands of skills.

最好的方法是在表中代表候选人？我在考虑 skill_1 ， skill_2 ， skill_n 等，但是在某个地方，有一个候选人的技能超过 n 。

What's the best way to represent the candidate in a table? I am thinking skill_1, skill_2, skill_n, etc, but somewhere out there there is a candidate with more than n skills.

此外，可能还有更多的技能

Also, it is possible that more skills will be added to the database in future.

那么，代表候选人技能的最佳方法是什么？

So, what's the best way to represent a candidate's skills?

@zohar的[更新]，这是在架构上的第一步。有任何评论吗？

[Update] for @zohar, here's a rough first pass at teh schema. Any comments?

推荐答案

您至少需要三个表：

一个表用于候选人，其中将包含名称等所有详细信息，联系信息，简历（或指向该简历的链接）以及所有其他相关详细信息。

One table for candidates, that will contain all the details such as name, contact information, the cv (or a link to it) and all other relevant details.

一个用于显示技能的表格-其中将包含技能名称，以及简短说明（如果相关）

One table for skills - that will contain the skill name, and perhaps a short description (if that's relevant)

和一张表格，将候选人与技能相关联- candidatesToSkills -两个表具有1对多关系-主键是候选ID和技能ID的组合。

这是创建多对多关系的关系方式。
作为奖励，您还可以添加技能级别列-初学者，英特

and one table to connect candidates to skills - candidatesToSkills - that will have a 1 to many relationship with both tables - and a primary key that is the combination of the candidate id and the skill id.
This is the relational way of creating a many to many relationship.
As a bonus, you can also add a column for skill level - beginner, intermediate, skilled, expert etc'.

您可能还想添加一个职位空缺表，并将另一个表连接到技能表，这样您就可以轻松地根据所需技能找到最合适的人选。 （但请注意，技能并不是唯一需要的技能，其他要匹配的要素是地理位置，薪资期望等。）

You might also want to add a table for job openings and another table to connect that to the skills table, so that you can easily find the most suitable candidate for the job based on the required skills. (but please note that skills is not the only match needed - other points to match are geographic location, salary expectations, etc'.)

这篇关于MySql表可能*非常*很多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！