Designing a Mysql database with multiple values in a field

Question

I'm a student doing my final year. I am supposed to create a database for my project such that it stores multiple values under a Skills column. For one one user id there can be multiple values in the skills field.

What is the best way to proceed ? I'm supposed to create in Mysql.

I looked it up but it looked like it will take a lot of time. I'm on clock for my project. Is there any other way ? — user201537, Oct 09 '16 at 07:03
It is the only sensible approach. Every other way will produce more problems later. — Gerald Schneider, Oct 09 '16 at 07:10
see the concept of junction/intersect and association tables like [Here](http://stackoverflow.com/a/32620163) . If you are under the gun, then doing it with CSV values will just slow down the dev anyway. — Drew, Oct 09 '16 at 07:13

score 1 · Answer 1 · answered Oct 09 '16 at 07:24

1

The best practice would be to use two tables for this purpose.

UserTable:
Id    rest of the appropriate fields
1        ...........
2        ............

SkillSetTable:
id UserTable_id         skillName
1     1                   abcd
2     1                   pqrs
3     1                   hijk
4     1                   lmnop

Here you can use foreign key to point to UserTableid. That way you dont have to bother about number of skills

answered Oct 09 '16 at 07:24

Mustofa Rizwan

10,215
2
28
43

A surrogate key serves no useful purpose here – Strawberry Oct 09 '16 at 07:33
Which one are you referring to as surrogate key ? – Mustofa Rizwan Oct 09 '16 at 07:51
id in SkillSetTable is surrogate – Strawberry Oct 09 '16 at 08:34
it is a common behavior to have primary key in a table, here id is primary key.... although you may think the rest two column can combine to be a composite key, yet it is up to designer's perspective – Mustofa Rizwan Oct 09 '16 at 08:57
Yes. It's surrogate. – Strawberry Oct 09 '16 at 10:42

Designing a Mysql database with multiple values in a field

1 Answers1