database search question

hi everyone

i have a question. I have a site where users can select their top 10 skills. Basically the form consists of 10 different check boxes and the users can check as many as they want where each check box corresponds to a particular skill.

Now this is my question. After the users select their skills, should i get the check box values and concatenate them into one string seperated by comma's and store that one string in one text field of the database or should i have 10 different columns in the database to store these values. Is this a good practice.

Now based on these values, I need to perform a keyword search to find a user of a particular skill.

What I was planning to do is to store all the skills of a particular user as one string (seperated by comma) in the database. And while searching by keyword , retrieve this string stored in the database and check if the entered keyword exists in that string or not.

I would really appreciate any advice on the same. Is this the best way to do it. The programming language i'm using is java and the database is mysql.

thanks
leo

I have been working in SQL for only six months, so please take my recommendation only as a recommendation and not as the be-all end-all

I would not suggest using 10 columns for each of the skills. What happens when you want to add another skill? And it's going to be difficult (and slow) searching for one skill in 10 columns.

Neither would I suggest using only one column and throwing everything in there. First prob is: What happens if their are commas within the skill. Then the skill will show as two skills. And I don't really think it's good practise (then you might as well use a text file instead of a databse).

What I would suggest is using two table (well actually three, but I assume that you already have a table that stores the users with some sort of unique identifier with every user).

The first table will hold a skill id, and a skill description. Your page can then just run through the table, and find display all the descriptions. This is very quick to develop (the web page), easy to create, and easy to add more skills should you need to. An example of the table:
siSkillId vcDescription
1 Catching bugs
2 Eating bugs
3 Watching bugs
4 Killing bugs

The second table will also have only two columns, one for the userId, and the other for the skill ID. Since one user can have more than one record in this table, this takes up a bit more space, but I think the advantages are worth it. It will be easier to search through it, easy to add another skill for a user. You can even add another column to show the order of the skills (in which one the user is best)
example of the table:
siUserId siSkillId
1 1
1 2
1 3
1 4
2 3
4 1
4 2

hey
thanks for ur advice. I really appreciate your time. I'll look into ur idea and see what best suits my needs. But it was good to learn abt your idea...thanks again
leo

Glad I could help. It's a shame nobody else replied. I would have liked to learn more idees as well (South Africa is usually a bit behind on new idees ).

Oh yes, I never said this, but between your two idees, I'll go for the throwing everything in one column thing. I think the advantages of faster search time will more than make up for the more development it's going to cost you.

that the idea of having multiple tables is the best idea. After all, you are working in a relational database, aren't you? Besides, with proper indexing (you were planning on indexing your columns for rapid retrieval, right?) and the use of integer values instead of strings, you should be able to write a stored procedure to retrieve datasets that specifically match your query, format it for display, and dump it into a result set for processing in your CGI simply, easily and very quickly. My favorite is to do most of the formatting in the procedure and have rather generic display routines in the CGI, that way I can make changes willy nilly and never touch production code.

BTW: If your database starts to get really big (I have worked with tables with 15 million+ rows and have millisecond return times) you can begin to use temporary tables in your procedures to improve your performance even more (I have been able to get up to 2 orders of magnitude performance increases by using temp tables). My experience is mostly with Sybase and MSSqlserver, but the same approach should work in any (real) database.

Good luck!