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
– user201537, Commented Oct 9, 2016 at 7:03
It is the only sensible approach. Every other way will produce more problems later. — Gerald Schneider
– Gerald Schneider, Commented Oct 9, 2016 at 7:10
see the concept of junction/intersect and association tables like Here . If you are under the gun, then doing it with CSV values will just slow down the dev anyway. — Drew
– Drew, Commented Oct 9, 2016 at 7:13

Mustofa Rizwan · Accepted Answer · 2016-10-09 07:24:37Z

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 9, 2016 at 7:24

Mustofa Rizwan

10.5k2 gold badges30 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Strawberry Over a year ago

A surrogate key serves no useful purpose here

Mustofa Rizwan Over a year ago

Which one are you referring to as surrogate key ?

Strawberry Over a year ago

id in SkillSetTable is surrogate

Mustofa Rizwan Over a year ago

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

Strawberry Over a year ago

Yes. It's surrogate.

Collectives™ on Stack Overflow

Designing a Mysql database with multiple values in a field

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related