Attendance Database with Excessive Absence Alert

I have a pretty standard attendance database design, but I want to be alerted when a student hits a certain number of absences. I was thinking to use a point system where each absence would accumulate a certain number of points (tracked on the student table itself) it would throw a message. The DB is currently in Access 2003, but I'm probably going to move it to MS SQL Server in the near future.

Here's my design idea, just want to make sure it's logical and normalized. I've bolded the columns I'm most concerned about.

tblAttendance -

• PK AttendanceID
• FK EventID
• FK StudentID
• Date AttendanceDate

tblEventEnrollment -

• PK EventEnrollmentID
• FK StudentID
• FK EventID

tblEvents -

• PK EventID
• FK EventType
• nvarchar EventName
• FK EventLeader

tblEventTypes -

• PK EventTypeID
• nvarchar EventType
• int PointsIfMissed

tblStudents -

• PK StudentID
• nvarchar FistName
• nvarchar LastName
• int CurrentPoints

EDIT Using a CurrentPoints column in tblStudents is based on two factors:

1. To avoid excessive joins/aggreg开发者_如何学Goation. One student may have 10 events a day, 3650 a year, and over say 10 years 36,000+ event attendance records to be checked to get his score. (I have not done any tests to see the actual performance impact once the data set gets big)

2. To allow me to reset the points. I also considered using a Date LastPointResetDate column in tblStudents and then using a query along these lines to calculate the score, I was just worried about performance (keep in mind I want to check this with every new attendance record):

   SELECT SUM(tblEventTypes.PoinsIfMissed) AS CurrentPoints FROM tblAttendance INNER JOIN tblEvents ON tblAttendance.EventID=tblEvents.EventID INNER JOIN tblEventTypes ON tblEventTypes.EventTypeID=tblEvents.EventTypeID WHERE tblAttendance.AttendanceDate > tblStudents.LastPointResetDate.

---

At a quick glance, why does events have :

FK EventType

Rather than

FK EventTypeID

It seems to me that a student can only miss an event if they are enrolled for that event, so rather than int CurrentPoints in the student table, get the points from a LEFT JOIN from enrollment to attendance to find the missed events.