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 Answer 1

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.

4
  • Oh sorry I have the foreign keys for both of them, but what else would you recommend? (i edited that)
    – edgarmtze
    Oct 29, 2012 at 16:47
  • @cMinor, what do you wish to accomplish by having Person.Skills_ID and Person.Proyect_ID FKs?
    – Roflo
    Oct 29, 2012 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.}
    – edgarmtze
    Oct 29, 2012 at 17:01
  • @cMinor Sorry, I missed the [facttable] tag. I'll leave this to the warehouse experts.
    – Roflo
    Oct 30, 2012 at 15:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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