Job Scheduling - Database Design

[brucevde]

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)?

[szlamany]

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

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

[kevchadders]

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.

[brucevde]

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

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.