Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

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

share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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