Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2006
    Posts
    3

    Advice on ERD: Employee Skillset application

    Hi all,

    I am just after some advice as the first time in history I am doing an ERD and get stuck at a point. If somebody has a solution to it, please step forward :)

    The application I am planning:
    System to track Employees and their skillset. And the main problem is: There are two ways an Employee can acquire a skill:
    1. By completing a training course
    2. By "just having it" (enter the skill manually)

    So the training courses are linked ot a skill ("If you complete this course you acquire skill XYZ"). An employee has a skill at a certain level (Low, Medium, High). If he acquires a skill through a course (e.g.: "MCDBA Bootcamp + Exam" course gives him the "MSSQL2000" skill at level 3-high), that skill "degrades" after a while. So after 6 months, that skill will be at Medium, after a year at Low if he does not do further training.

    Well, I think I know how to do the thing with skills and degrading them. But what I am struggling with is the fact that there are two ways an employee can acquire a skill. The skills he got through a course can easily be found out by joining the employees, courses and skills tables (with some n:m: table inbetween, obviously). But how do I get the skills which are manually entered for this guy?

    The only thing I though of was creating a trigger or Stored Procedure on some tables and then manually put all skills into a temporary (well no, rather fixed actually) table. So as soon as the course for an employee is marked as "completed", the Trigger kicks in and adds the skill which is associated to this course into another table. And we can then manually add stuff do this other table.

    But I dont actually like this. Rather like this to be solved within the design, not with code.

    Any hints?

    I have created the Schema in fabForce DBDesigner 4. You can get the SQL code (best used with MySQL) here:
    http://temp.mylansite.org/EmployeeSkills.sql

    The XML for DBDesigner (if anybody wants to play around with it and send it back to me):
    http://temp.mylansite.org/EmployeeSkills.xml

    And a Screenshot of the ERD so far:
    http://temp.mylansite.org/EmployeeSkills.jpg

    Any help greatly appreciated!

    Cheers

    Samuel

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    add a many-to-many table called students_has_skills

    put everything in there, either manually entered or computed

    whether you do this by trigger or some other way is not germane to the date model
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2006
    Posts
    3
    Thanks for the answer.

    So you say as well that there is no other way to solve this than maintaining another table?
    Or creating "fake" courses. So if you want to directly assign a skill to an employee you just mark this fake course as "completed" for him. This way, all skills would be acquired through course completion.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i did not say that there is no other way -- there are probably lots of other ways

    i just like the students-has-skills relationship because it manifests the reality of which students have which skills

    k.i.s.s.

    "all skills would be acquired through course completion" is wrong, because it doesn't mirror reality
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2006
    Posts
    3
    Quote Originally Posted by r937
    i did not say that there is no other way -- there are probably lots of other ways
    Ok, right.

    i just like the students-has-skills relationship because it manifests the reality of which students have which skills
    Which does make sense

    "all skills would be acquired through course completion" is wrong, because it doesn't mirror reality
    That's why I did not like this solution. Call it a "workaround". But I like the thinking: I should not do something in the Database just to solve a problem if the result in the Database then does not mirror reality anymore. I have to keep this in mind. I am a guy who is over-normalizing some times and then trap myself into situations like these when I want to have a 1'000% normalized ERD and end up joining 15 tables to get my result when it would be so much simpler using solutions as the one we are discussing now

    thanks again.

Posting Permissions

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