course attendance and expiration table design

[selahlynch]

I'm having a hard time deciding how to set up my class diagram for a database that keeps track of employee training records. I need to keep track of when employees attended certain courses. I also need to keep track of dates by which employees are required to take a course.

Its also very important that I am able to compute a statistic that is the compliance percentage. That is a ratio A/B.
A - the number of employees who have fulfilled the requirements for a certain course
B - the number of employees who are either required to take the course OR have fulfilled the course requirements.

Below I attached some possible class diagrams. I'm looking for any kind of feedback or sharing of experience. I feel a little bit stuck right now.

 

[vbaInet]

See if this helps:

Many courses to Many Employees
ONE course taken by an employee to ONE requirement.
ONE course taken by an employee to ONE attendance record for that course.

 

[selahlynch]

Hey, I'm not totally sure, but is this diagram representative of what you were suggesting?

One thing... an employee may attend a given class multiple times so i changed that in the diagram.

Also, would you recommending making a separate class for Expirations?

I know you are supposed to break these classes and tables up to make them properly normalized, but then I'm not sure if that will make my life more difficult when I'm trying to manage the database.

Thanks for your help

 

[vbaInet]

From what you've just mentioned I think you're on the right track. You could change your EmployeeEvent table to EventsAttendance so its a more intuitive name.

What is the Expirations table supposed to be for?

 

[selahlynch]

The expiration table is because I need to somehow keep track of when an employee is due to take a course again. For example some courses need to be taken every six months. Also, sometimes a person needs to take a course that they haven't taken before, so I set the expiration date to yesterday to make it clear that they are expired and must take the course as soon as possible.

 

[vbaInet]

I was thinking a NextOccurenceDate could be included in the EmployeeEvent table and when pulling up the data you check whether the CourseDate is Less Than the NextOccurenceDate. Something like that could work for you?

If you wanted those course that an employee hasn't done, you could use the Events table and the EmployeeEvent table, change the join to an Left Outter Join and check I Not Null.