Scheduled Report(task) Monitor

Question

I have to develop a system to monitor the generation/transmission of reports.

• System data will be stored in database tables (Sybase)
• Reports will be generated with different schedules ("mon-fri 10pm", "sat 5am", "1st day of the month", etc.)
• System will just monitor that the reports were created. It will not create the reports itself.
• System will notify appropriate personnel when a report did not finish.
• System will maintain a log of all generated reports

Anyone know of a good(tried and proven) table(s) design for storing the task shedules?. I already have an idea, but I don't want reinvent the wheel.

Answer 1

If you really are going to support complex schedules of all those types, I am not sure it is a good idea to try to invent an equally complex relational DB schema for all the details of those schedules.

I would consider designing an XML schema for schedules' details and, if you really need to store schedules in a relational database, storing the XML data in a column. You could use columns for those attributes of schedules that are applicable to a schedule of any type (like schedule name or who modified it last time and when).

For example, assuming that a schedule can be used for more than one report, you could do something like this:

Table: Schedule
---------------
Columns:
    ID                  - Surrogate key to refer to schedules
                          from other tables.

    Name                - Short textual description of the schedule
                          (to be shown in GUI).

    ...

    Details             - XML containing all the details of
                          the schedule (frequency, exceptions,
                          complex combinations of simple schedules, 
                          whatsoever).

Even if your application has to answer questions like "what reports are supposed to be due by a given date/time", I think you must have really, really large number of schedules to justify the overhead of using relational schema to store nitty-gritty schedule details in separate columns (and, perhaps, in multiple tables).

Answer 2

You can create a table schedule with the columns

• id
• name
• mon, tue, wed, thu, fri, sat, sun
• day
• month
• year
• time (h*60+m)

your examples would be:

mon-fri 10pm

mon, tue, wed, thu, fri = true, time=22*60, the rest are NULL

sat 5am

sat=true, time=5*60, the rest are NULL

1st day of the month

day=1, the rest are NULL

You could also have a table reports with the columns

• id
• name
• schedule_id

and a table report__schedule___late with the columns:

• id
• date_time
• report_id