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