New project, advise appreciated
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 :D.
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! :p)
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.
I think we've found our mole Fou-Lu :thumbsup: :p
On a more serious note, any ideas? I'd hope to start coding today but am reluctant to do so until I get some opinions...
And for those who are scared off by Tangos remark, it's for a community project located solely in the Netherlands, so nothing to fear :p
Option two is a lot of hassle and you've already described the downside yourself.
Option 3 will make it difficult to sort through your members by skill as mysql won't sort through the arrays in each record (well not that I know of anyway - if someone cares to correct me I'll be glad to learn as I could use this myself!).
Option one from my POV is the only way of doing it.
PS my comment was a joke.. I simply didn't reply to your question because I had square eyes at the time and couldn't really understand it lol (that'll teach me for staying up late again lol)
Forgot to post this..
I have a couple of golden rules for database projects:
1) Never require the code to adjust the structure of the database - columns, types etc
2) Never duplicate data - if it needs duplicating it needs moving out into another table with a foreign key
3) Never store data in a format that can't be sorted by mysql. You may not be planning on it now but at some point you'll want to be able to sort through it as the tables rows grow.
Hope that helps :thumbsup:
Thanks for the comment, you're thinking along the same lines as I am. Option 1 it is then =)
About your second post with the 3 rules, #2 and 3 are always in my mind. I don't fully understand your first rule though, could you please explain what you mean with that one?
You were talking of adding extra columns to your table. Never change the structure of a table directly from your code. The moment you do that you're opening up the possiblity of corrupting it at some point or destroying something important. Structure changes to tables should only be done manually via phpmyadmin.
Ah, that explains! I get what you mean and don't worry, I never do that. Any change in database structure is always done from phpMyAdmin and then the code is adapted ;)
|All times are GMT +1. The time now is 08:01 AM.|
Powered by vBulletin®
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.