How to design this kind of relations in a database

database-designfacttable

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?

Best Answer

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.

Related Question