Database Modeling an Email List and Report Types

Question

I'm having a huge problem in modeling this problem. I'm writing a exam taking application (in MS-Access, but this isn't really relevant) and there are a few reports that need to be sent out.

The first report is sent out automatically after a person completes an exam. The second report is sent out every 2 weeks (or whatever they want to set it to).

The content of these reports contain exam information. Based on the exam type, certain people need to be emailed. Also, based on the frequency (per exam or per 2 weeks) there needs to be a differentiation as well.

I have some real reservations about how I'm setting this up. I really don't know if this is correct or not and it'll be a huge amount of work if the database schema isn't set up correctly.

The proposed schema is below:

-----------
|Exam List|
-----------
    \|/
     |
     |
-----------
|Exam Type|
-----------
     |
     |
    /|\
------------    -----------
|Email List|>---|Frequency|
------------    -----------
    \|/
     |
     |
-----------
|Employees|
-----------

What do you guys think? I'm afraid that the way the frequency is set up that it won't describe fully the data and I'll have to hack together tons of IF statements within Access to accomodate (something I'm trying to avoid, unlike the previous program that is being replaced by this).

Answer 1

Are employees taking the exams? I think you're missing at least one relationship between employees and exams (employeeExam, where you'd store things like dateExamTaken and score, and maybe a flag like resultsSent)

It might help to also include a mailSent table for auditing and debugging purposes.

Answer 2

Well, this may be a start.

• A Test is an action of an Employee taking an Exam. An employee may take an exam several times.
• An Exam is of a certain ExamType.
• A person receiving the email (Recipient) is an employee too.
• Each Recipient can schedule (EmailSchedule) a different email (Template) for each ExamType at a specified Frequency.
• According to the EmailSchedule, emails are queued into the EmailQueue for each Test.