CODEWITHSUNDEEP
mysqldatabasealgorithmsearchstoring-data
ninja.coder
ninja.coder

Reputation: 9658

Best technique to implement skill matrix

Recently I was asked to design a portal where students can upload their technical skills like: HTML, PHP, C++ etc. and college can search the students based on the mandatory and optional skills.

Initially I thought of storing the student and their related skills details using a two columned table where first column represents the student and second column consists of all the skills related to that student separated by comma. For example, 001 HTML,C,C++,JAVA 002 C,JAVA 003 HTML,.NET ... ...

But now I'm facing problems in querying and searching for the student who have relevant skills. I tried a lot but end up with no clue on how this can be achieve.

Any pointers on storing techniques and searching algorithms etc. would be helpful. Help is much appreciated.

Thanks

Upvotes: 0

Views: 554

Answers (1)

Ruaidhrí Primrose
Ruaidhrí Primrose

Reputation: 1250

A simple way to handle this is to have three tables. The first table contains students and includes a unique ID for each student. The second table contains skills and has a unique ID for each skill. The third table is for cross-referencing the first two and has two columns: "Student ID" and "Skill ID".

E.G. Let's say the skill table has an entry for "C" with ID 1, and for "C++" with an ID of 2. We have a student with an ID of 1 who knows both C and C++. We put two entries in cross-reference, the first entry has a student ID of 1 and a skill ID of 1, the second entry has a student ID of 1 and a skill ID of 2.

When you need to find out what skills a student has you just find all the rows in the cross reference table for that student ID. Conversely, if you want to find out what students have a certain skill you just find all the rows with that skill ID.

Upvotes: 1

Related Questions