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

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

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.

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

Ok, right.

Which does make sense

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.