Re: data modelling question - job vs. job history table

Jacques,

What you are doing is not exactly data modeling: you are designing a database.

When data modeling, the database to be used is irrelevant. There are no columns
or primary keys, but there are attributes, and one or more unique identifiers.

>From a DM perspective, I would be first inclined to think of JOB as a
super type,
with 3 subtypes of SCHEDULED, UNSCHEDULED and COMPLETED, each with their unique attributes. I'm not too sure about the UNSCHEDULED though, it sounds
as if that just really containts the base attributes for the job, though this 5 minute
email hardly constitutes a modeling session. :)

The attributes in common wuld be just the job_no as far as I can see.

UNSCHEDULED should probably be called something like CONFIG.

CONFIG would contain the interval, earliest start date, and maybe a latest_start_date,
past which the job would not run, along with whatever else is needed to describe the
actual process that is to run.

SCHEDULED would contain the start_date and time, as well as anything else that
is needed at runtime. This makes me reconsider the value CONFIG subtype. It could
very well be that these attributes should be in the supertype JOB. What would drive
that would be whether you want changes made to a jobs configuration to affect
scheduled jobs or not. If so, they go in the supertype, if not, then into the CONFIG
subtype they go.

This DM would leave you free to design the database with 1-4 tables to implement
the JOB supertype and subtypes. I like the idea of keeping only jobs that are scheduled
in one of the tables, and deleting them upon completion.

HTH Jared

"Jacques Kilchoer" <Jacques.Kilchoer_at_quest.com> Sent by: ml-errors_at_fatcity.com
 11/12/2003 04:49 PM
 Please respond to ORACLE-L  

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        cc: 
        Subject:        data modelling question - job vs. job history table

Warning - this is a little long.
Thank you to those who take the time to read it. I have a data modelling question (the target database will be an Oracle database.) I am keeping track of scheduled jobs run by a job agent.

Table 1:
JOB with columns JOBNO (primary key), JOBNAME

Table 2:
JOB_SCHED with columns JOB_SCHED_ID (primary key), JOBNO (foreign key to JOB), JOB_INTERVAL, JOB_START_DATE, JOB_END_DATE, JOB_RESULT Table JOB_SCHED can have:
completed jobs: JOB_START_DATE not null and JOB_END_DATE not null scheduled jobs: JOB_START_DATE not null and JOB_END_DATE null unscheduled jobs: JOB_START_DATE null and JOB_END_DATE null The job can be scheduled to run only once: JOB_INTERVAL null or scheduled to run periodically: JOB_INTERVAL not null

A user can save an unscheduled job and then schedule it later. As currently designed JOB_SCHED contains job history for past jobs.

My background scheduler often looks up jobs to see which jobs should run now. If JOB_SCHED contains the history of all jobs run then I will have to scan through many rows to find out those jobs which should run now.

I could do this in several ways:
Option 1: put completed jobs in a different table called JOB_HISTORY, and then JOBNO would be UNIQUE in JOB_SCHED, or I could combine the columns in JOB and JOB_SCHED
Option 2: select * from JOB a, JOB_SCHED b where a.JOBNO = b.JOBNO and b.JOB_START_DATE is not null and b.JOB_END_DATE is null

But I propose option 3:
Add to JOBNO a column called CURRENT_JOB_SCHED_ID (foreign key to JOB_SCHED)
This should make it faster to find the current schedule for the job. The tables have reciprocal foreign key relationships: JOB_SCHED.JOBNO foreign key references JOB.JOBNO -> FK_JOBNO JOB.CURRENT_JOB_SCHED_ID foreign key references JOB_SCHED.JOB_SCHED_ID -> FK_JOB_SCHED FK_JOBNO characteristics: ON DELETE CASCADE FK_JOB_SCHED characteristics: DEFERRABLE INITIALLY DEFERRED (you insert into JOB before you insert into JOB_SCHED)

On JOB I have a BEFORE INSERT TRIGGER that generates JOBNO and CURRENT_JOB_SCHED_ID based on a sequence On JOB_SCHED I have a BEFORE INSERT TRIGGER that generates JOB_SCHED_ID based on a sequence if JOB_SCHED_ID is null

To create a new job:
insert into JOB returning the new JOBNO and CURRENT_JOB_SCHED_ID set by trigger
-- the insert into JOB will succeed because the FK relationship to JOB_SCHED is a DEFERRABLE FK constraint
insert into JOB_SCHED using the schedule ID returned by the above insert commit

When a periodic job has completed:
update JOB_SCHED set JOB_END_DATE = SYSDATE insert into JOB_SCHED returning the new JOB_SCHED_ID set by trigger, START_DATE = previous START_DATE + INTERVAL update JOB set CURRENT_JOB_SCHED_ID to the schedule ID returned by the above insert
commit

When a "run-once" job has completed:
update JOB_SCHED set JOB_END_DATE = SYSDATE update JOB set CURRENT_JOB_SCHED_ID to null commit

Is there any reason why option 3 should be avoided?

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jacques Kilchoer
  INET: Jacques.Kilchoer_at_quest.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: Jared.Still_at_radisys.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).