0

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!!

xTMNTxRaphaelx
  • 1,387
  • 3
  • 11
  • 14
  • What are the tables' relations? Is `ScheduleId` a FK to the `ReportSchedule` table? Does a `ReportSchedule` have multiple daily/weekly/monthly schedules? – geomagas Nov 07 '13 at 09:39
  • ScheduleId is PK to Report Schedule. Yes,ReportSchedule can have multiple schedules – xTMNTxRaphaelx Nov 07 '13 at 09:42
  • I'm referring to the `ScheduleId` column _in the other tables_, obvously. And you'll have to describe your application logic a little more. Give some example data that cover all cases. – geomagas Nov 07 '13 at 09:46
  • ScheduleId is FK to other tables. Example: A user need to send report named ABC on every Monday and Thursday at 3:00PM – xTMNTxRaphaelx Nov 07 '13 at 10:10
  • So that would be two rows in the `ScheduleDaily` table with a `ScheduleId` of `ABC`. Right? Got it. – geomagas Nov 07 '13 at 10:13
  • Yes actually but I want the design that does not need 2 entries. – xTMNTxRaphaelx Nov 07 '13 at 10:16

1 Answers1

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.

geomagas
  • 3,230
  • 1
  • 17
  • 27
  • sounds pretty cleaner but not understood the 2nd point.Kindly elaborate with an example. – xTMNTxRaphaelx Nov 07 '13 at 11:43
  • 1
    Well, having an `EveryHours` field enables you to schedule a task to run every X hours. If `EveryHours=24`, the event runs once a day. If `EveryHours=8` thrice a day and if `EveryHours=192` once a week. – geomagas Nov 07 '13 at 11:54
  • If I add a schedule to send mail every Monday,Tuesday at 3:00PM of the month January then how the entries would be added?? – xTMNTxRaphaelx Nov 08 '13 at 05:14