How do you recommend I handle a database design where a USER can have many SKILLS?

Question

Here's my database design:

USER                  SKILLS
id                    idUser
email     1-------*   description
name                  selfAssesedLevel
lastname              yearsExperience
?skills?              reference

How could I handle this? I'm kind of lost on what I have to write down in ?skills? field of the USER table.

My purpose is to have a user be able to add n number of skills to his profile.

Thank you.

Answer 1

You don't need the skills field in the USER table, as you already have a link between user and skills: the SKILLS.idUser field.

So when you want to add a skill for a user with id 666, you should just add a new entry in the SKILLS table, having idUser = 666

Extra: I would recommend to add an extra field to the SKILLS table: 'id' or 'skillsid', which can be the primary key for this table.

Answer 2

You don't need a ?skills? column at all in the USER table. The existence of a row in the SKILLS table with a particular idUser is enough.

For example, to select all the skills for user id 4, you would write:

select description, selfAssesedLevel, yearsExperience, reference from SKILLS where idUser = 4

Answer 3

Use three tables, while you don't NEED three for a one to many relationship, things change, make the db support it, even if the app won't ...yet.

And you do have a many to many, as your skills should be their own table, as two people will share the same skill, what's different is their SelfAssesedLevel, YearsExperience and Reference

Users
----
UserId
Email
Name
LastName


Skills
------
SkillId
Description


UserSkills
----------
UserId
SkillId
SelfAssesedLevel
YearsExperience
Reference

Then your SQL to get the users and their skills would be something along the lines of

SELECT * FROM Users 
    JOIN UserSkills ON Users.UserId = UserSkills.UserId
    JOIN Skills ON UserSkills.SkillId = Skills.SkillId

Answer 4

Since a User can have many Skills, and a Skill can be common across many Users, you need a many-to-many JOIN table UserSkills. Users and Skills will have a foreign key relationship with UserSkills.