0

I’m designing a new database where I have two tables, a Users and a Skills table. A user can have many skills. What is best practice for storing multiple skills, a comma delimited skill ids (1,2) or store the skill name (PHP,Ruby)?. Here is simplified version of the database.

Users table:

id int(),
name varcha(),
email varchar(),
skillID varchar()

ID  | name      | email                 | skillID   |

1   | John Doe  | johndoe@domain.com    |1,2        |

2   | Jane Doe  | janedoe@domain.com    |2,3        |

3   | James Smith| jamessmith@domain.com|3,4        |

Skills table:

Id int(),s
skill varchar()

ID  | skill       |

1   | PHP         |  
2   | Ruby        |    
3   | ColdFusion  |
4   | ASPX        |

How would one retrieve all users with skill ID of 1 and 4. Thank you in advance for your insights.

Nagaraj S
  • 13,316
  • 6
  • 32
  • 53
user752746
  • 617
  • 9
  • 31
  • 1
    No do not store comma separated ids it will raise many problems you should design your structure in normalized way have a look at [*Normalization in MYSQL*](http://stackoverflow.com/questions/1258743/normalization-in-mysql) – M Khalid Junaid Feb 03 '14 at 07:07
  • Look at the many questions asking for splitting comma separated fields with *SQL. Usually, it's not worth the trouble. – Olaf Dietsche Feb 03 '14 at 07:11
  • 1
    I would recommend making a mapping table such as user skills table where you can map skills to users. it is safer to do that than using comma separated values – Akhil Sidharth Feb 03 '14 at 07:11
  • Check the Andrew Brown answer!!! – user2727841 Feb 03 '14 at 07:15

1 Answers1

6

to expand on @Akhil Sidharth 's comment.

user table
    userId
    name
    email

skill table
    skillId
    name

userSkills table
    userSkillId
    userId
    skillId

EDIT:

I missed the very last part of your question.

SELECT * FROM `userSkills` as `us` LEFT JOIN `user` as `u` ON us.`userId` = u.`userId` WHERE us.`skillId` = '1' OR us.`skillId` = '4'`
Andrew Brown
  • 5,330
  • 3
  • 22
  • 39
  • Hi Andrew, thank you for your response. I hadn't thought about doing it this way before. I'll give it a try. thanks again for your insights! – user752746 Feb 03 '14 at 07:40
  • glad to help. this concept is the heart of `normalization`. if you've got some free time, I would suggest reading up on it as it will benefit you immensely in the long run. http://en.wikipedia.org/wiki/Database_normalization – Andrew Brown Feb 03 '14 at 07:46
  • Thank I've applied your suggestion and it worked like a charm. I'll take at normalization when I have some free time, thanks again. – user752746 Feb 07 '14 at 05:59