0

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?

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.

  • Oh sorry I have the foreign keys for both of them, but what else would you recommend? (i edited that) – cMinor Oct 29 '12 at 16:47
  • @cMinor, what do you wish to accomplish by having Person.Skills_ID and Person.Proyect_ID FKs? – Roflo Oct 29 '12 at 16:59
  • to avoid a list of skills bacause a person could have several skills, the person.proyect_id key is to know the list of the projects a persons has done.} – cMinor Oct 29 '12 at 17:01
  • @cMinor Sorry, I missed the [facttable] tag. I'll leave this to the warehouse experts. – Roflo Oct 30 '12 at 15:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.