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
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.