Design DB Structure for Mail Scheduling

Question

I need to design Database structure to mail report scheduling. As far till now,I came forward with this design as follows:

    **ReportSchedule**
    - ScheduleId
     - ReportName
     - Subject
     - To
     - UserId
     - Body
     - Remarks

**ScheduleDaily**

 - Id
 - ScheduleId
 - StartDate
 - EndDate
 - SendTime

**ScheduleWeekly**

 - Id
 - ScheduleId
 - StartDate
 - EndDate
 - SendTime
 - DayOfWeek

**ScheduleMonthly**

 - Id
 - ScheduleId
 - StartDate
 - EndDate
 - SendTime
 - MonthOfYear
 - DayOfWeek

..................

I am not satisfied with this design and I need single table which covers all parameters (probably which includes reccurence rule). Kindly suggest!!

Answer 1

1. Normalizing

As a first step, I see that most of the columns in the Schedule* tables are common. So you could move them in a ScheduleCommon table. But leave an id column behind, which will be the PK of the remaining tables and a FK to the ScheduleCommon table at the same time. That makes it an IS-A relation.

The above step will actually add an extra table to your schema, but IMHO it's a necessary normalization step.

2. Globalizing the recurring rule

I'm thinking, you could replace the recurring rules in your tables with an EveryHours field and a LastRun one. That way you can determine if LastRun+EveryHours has come to past, and the job needs to run again (and update the LastRun field).

The above will eliminate the Schedule* tables, since those fields are common and can be moved to the ScheduleCommon table. That leaves you with only two tables.

3. Making it 1:1

Provided that each report schedule has only one recurring schedule, the relation of the two tables becomes 1:1 and the one can absorb the other. But I don't think this is the case. Let's examine the example you provided in the comments: "send mail every Monday,Tuesday at 3:00PM of the month January". That's not one schedule but actually two:

ScheduleId  StartDate           EndDate             EveryHours
ABC         2014-01-06 3:00PM   2014-02-01 3:00PM   192
ABC         2014-01-07 3:00PM   2014-02-01 3:00PM   192

As you can see, you will have to maintain multiple schedules for the same task, which makes the relation 1:N.