Hello fellow coders,
I'm thinking of starting a new project, and I've been racking my brains to find the best way to do things. I'd like you're professional opinion on the matter.
What I'm thinking of creating is some sort of gathering point of web-professionals, be it developer, designer or general webmaster of sorts. I know you'll be throwing rocks at me saying there are plenty of those out there, including this forum, LinkedIn and the like, but there is reason behind this madness
Now, the web-professionals are to create a profile, in which they can tick the boxes for the skills they possess, as shown in the image/attachment below:
Now the question is, what is the easiest way to store this information? I've come up with three possibilities so far.
Option 1: separate tables
I create a table (e.g. 'skill') with columns 'id', 'name' and 'parent'. In this table I enter all the skills currently available to tick, like so:
1 Developing Null
2 Designing Null
3 Template Null
4 PHP 1
5 HTML 1
6 WordPress 3
Another table is created with colums 'profile' and 'skill'. In this table each skill selected by the web-professional will be stored, like so:
The above would mean that profile #1 has PHP and HTML as skill, while profile #2 only knows WordPress-Templates (what a loser!
The bonus of this option is that amendments are easily made (just remove the row from the second table and the profile has lost that skill. Also it is easy to add skills when they become hot, like HTML6 or something.
Drawback would be that the second table would exponantially grow in size, as each profile could add 10, 20 or however many rows.
Option 2: In the profile table - part 1
The profile table keeps track of the profile details like name, e-mail address and the like. Now it would be easy to add extra columns there for each of the different skills.
Bonus is that adding or removing a skill is easy enough, just add another column or remove one. Drawback could possibly be a huge amount of columns in this table, as it would need to cover all skills.
Option 3: In the profile table - part 2
Another way is to store some sort of array in one column of the profile table, which would hold the skills. For profile 1 with PHP and HTML knowledge, the contents of this field would be something like 4,5. Upon reading this could easily be converted to an array of loose elements, the elements to be matched to the skill and then displayed on the page. Editing ones profile would mean replacing the contents of the field by a new string.
Bonus is that adding a new skill to the system would need no further action, as the profile-owner could select it once available and then save the profile, this would overwrite the current situation. Also the amount of queries are going to be limited to 1 per save/retrieval, and there's not going to be a heap of columns.
Drawback is that removing a skill is going to prove difficult, as this would mean either searching through all the profiles and removing the number associated with that skill, or wait until all profiles have edited their profile and remove the option to select that skill.
Any comments/alterations on my three proposed options are welcome, as well as other possibilities I haven't considered.