Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2003
    Posts
    59

    Unanswered: Help with database design

    Hi, I'm designing a new database which is mostly quite simple, but I've come across a sticky issue in one corner. I'm not sure I've done it in the best way, so I'd like some input.

    The real life scenario is that people are hired for jobs, they need particular skills to do those jobs.

    Each person has one or more skills, so these are modelled like this (simplified):

    TableUsers
    UserID (PK)
    UserDetails

    TableSkills
    SkillID (PK)
    SkillName

    TableUsersSkills
    UserID (FK)
    SkillID (FK)

    Each job needs one or more person:

    TableJobs
    JobID (PK)
    JobDetails

    TableJobsUsers
    JobUserID (PK)
    JobID (FK)
    UserID (FK)

    And the people needed are required to have one or more skill:
    JobUserID(FK)
    SkillID(FK)

    I can't think of a better way to do this. The problem is, that I'm not now sure how to run a query matching the skills required for a job to the people who have those skills. Any ideas?
    Thanks.

  2. #2
    Join Date
    Jul 2012
    Posts
    40

    Your ideas look OK

    Hi

    I have created a Data Model for you on my Database Answers Web Site :-
    http://www.databaseanswers.org/data_...sers/index.htm

    Your ideas look OK and I drafted some SQL.

    I look forward to your thoughts.

    HTH

    Barry Williams

  3. #3
    Join Date
    Apr 2003
    Posts
    59
    Hi, thanks so much for that. Your model is simpler than mine though, did you leave out what I called TableJobsUsers for a reason?

  4. #4
    Join Date
    Apr 2003
    Posts
    59
    I'd just like to clarify the issue with an example:

    The available skills are A, B and C.
    Person 1 has skills A and B, Person 2 has skills A, B and C, Person 3 has skill B.
    A job needs a first person who has skills A and B.
    I need a query that shows that Person 1 and Person 2 are suitable.
    The same job also needs a second person who has skill C. The query should show that only Person 2 is suitable.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •