paulgrahamster (3/23/2010)
One such rule you've built in states that no two functional areas can have a particular training course in common. Because you're linking a completed training course through the Employee_FunctionalArea table, it follows that you either need to put multiple rows in that table to indicate where that training course can apply to different functional areas for the same employee or you assume that a single instance of training only applies to a single functional area.Personally, I would have a separate table for the TrainingCourse and a bridge table (or link table or whatever you want to call it) for which training courses apply to which functional areas - no employee information involved. I would then have a bridge table linking simply employees to training courses.
That way, if an employee moves from Functional Area A to Functional Area B, we don't lose information about the training courses he's completed. If Functional Area B has some courses in common, the employee doesn't have to retake them just because he's moved to a new functional area.
I agree, with one change: in the employee-training bridge table, I would still include functional area information. Why? Because sometimes trainers will adapt their material based on their audience, without going through "the bother" of creating another class for it*. For example, suppose that an employee moves from cashier to store manager, and there is Loss Prevention training that they went through as a cashier shortly before being promoted. The date of training and date of promotion may be close enough to cause confusion, but if you can see that they went through the training as a cashier, you can decide that they may have missed some management-specific pieces, and ask them to retrain on it (nicely, of course.)
*Yes, I know it should be a different class. But this is the real world we're talking about. You can adapt your database to the real world, or you can try to alter the real world to suit your database. Guess which one might work out.