Job Scheduling - Database Design

I am trying to automate the scheduling of "Jobs" for multiple applications and am having trouble with the database design. Most of the "Jobs" will execute 1 or more "Steps". A Step is usually a Crystal Report but can also be a custom process (and allow for future expansion). "Jobs" can have multiple "Schedules". I used SQL Server's SysJob tables as a starting point and got this far.

Applications have
-> Jobs have
--> Schedules
--> Steps have
---> Input Parameters (typical Crystal Report parameters)
---> Output Locations (printers, emails, files)

The problem I am having is designing the Input/Output tables because even though Steps are the same for each Schedule, the Input and Output could be different. A single Step could be executed X number of times within a Schedule because there could be multiple Inputs for the Step. There could also be multiple Outputs for each Input. Sometimes there are no Input Parameters but again 1 or more Outputs. Obviously a Report has multiple parameters (Crystal allows a Parameter to have "multiple values").

Maybe I am overthinking the design and should just turn a Step into a "single execution of a report". So instead of a Job having 1 Step with 10 different sets of Input Parameters I would have 10 Steps with 1 Input parameter and multiple Output Locations.

I need some ideas on how to handle several tables all within a many to many to many situation (entity relationship tables aside because I don't think they will work in this case)?

Re: Job Scheduling - Database Design

Lay it out exactly like that - we do this in several places where the "loose" hierarchy of steps and levels is not exact

Code:

Appl Job Schedule Step Direction Flag1 Flag2 Value, etc... 1 1 1 1 I Year? valid=Yr 2008 1 1 1 1 I PO? 1 1 1 1 O .file .\SaveStep.tmp 1 1 1 2 I .file .\SaveStep.tmp 1 1 1 2 O .printer HPDeskJet

You can add extra levels between STEP and DIRECTION to have all kinds of sub-levels and conditional levels.

You are kind of in the realm of 4th, 5th and 6th levels of normalization - where the "memory" and "application" use of the data is much more important then the "storage" of it.

Post 5 and 8 of this thread dives a bit into this

http://www.vbforums.com/showthread.p...56#post3230256

Re: Job Scheduling - Database Design

Sounds like a job for a two way junction tables.

This is where you can split two tables, where you will have a Many to Many relationship, into into 3 tables. The extra table will be placed in between your two main tables. You will then create a Many to One relationship coming from both your main tables into the extra table you created.

Here is an example i did in another post which will might explain it better.
http://www.vbforums.com/showthread.p...81#post3329581

This is the best way i've found of handling many to many relationships between tables.

Also bruce, if you have a Entity Relationship Design, post it in here so that we can take a look.

1 Attachment(s)

Re: Job Scheduling - Database Design

I started out thinking I would need several "junction tables" (aka Entity Relationship tables) but ended up using only 1 and basically used a combination of the two suggestions.

Here are some examples of what needs to be generated

Code:

Command Output Output Value Parm Name Parm Value Parm Name Parm Value Department Report Printer \\PrintServer\2xlsr Dept Id 2X As of Date Yesterday Department Report Printer \\PrintServer\3ylsr Dept Id 3Y As of Date Yesterday Department Report Printer \\PrintServer\hawlsr Dept Id HH1,HH2,JH As of Date Yesterday Client Listing Printer \\PrintServer\sry5050 : No Parameters in this report Client Listing Printer \\PrintServer\pg4250 Client Listing Email [email protected] Client Listing Email [email protected] Sales Report Printer \\PrintServer\pg4250 YTD Yes Summary Yes File \\server\share\folder\file Sales Report Printer \\PrintServer\pg4250 YTD No Summary No File \\server\share\folder\file

I attached the Entity Diagram. I linked the "Output" table with the "Parameter" table. So even though a report gets sent to 1 or more Outputs using the same Parameters, the parameter information is duplicated. But I think this way the processing and maintenance logic would be simplified.

I know this post is pretty cryptic so any feedback is really appreciated.