How to design this kind of relations in a database

Question

I have a conceptual problem and I would like to get your ideas on how I'll be able to do what I am aiming.

My goal is to create a database with information of persons who work at a place depending on their profession and skills.

I have 3 categories which can have subcategories:

• Outsourcing
• Technician
  • welder
  • turner
  • assistant
• Administrative
  • supervisor
  • manager

So each person has its information and the projects they are working on, also one person may do several jobs...

I was thinking about having 3 tables (PERSON, SKILLS, PROYECTS) but I guess there is a better way of doing this.

create table Person
(
    [Person_ID]  int(10),
    [Name]       varchar(30),
    [sex]        varchar(10),
    [address]    varchar(10),
    [profession] varchar(10),
    [Skills_ID]   int(10),
    [Proyect_ID]   int(10),
    [Salary]     float
)

create table Skills
(
    [Skills_ID]  int(10),
    [Person_ID]  int(10),
    [Skills_name] varchar(10),
    [Skills_pay]  float(10),
    [Comments]    varchar(50)
)

create table Proyects
(
    [Proyect_ID]     int(10),
    [Person_ID]      int(10),
    [Proyect_name]   varchar(10),
    [working_Hours]  float(10),
    [Comments]       varchar(50)
)

Is there a way to do this in a more effcient way? What to add or delete of this small model? I guess I am missing something in the salary - maybe I need another table for that?

Answer 1

You should add more tables. Instead of Skills and projects being fields in Person, you should have PersonSkills (PersonID, SkillID) and PersonProjects (PersonID, ProjectID)

Also, Salary/Skills_Pay shouldn't be a float, and sex, probably not an int.