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

My personal preference is with solution 2 - moving the current information to JOB. The scheduler can quietly insert into JOB_HISTORY when it is done with a job, and update the current line (do it through triggers if you like). Solution 3 violates the beloved KISS principle ... Moreover, when you want to do some maintenance operation over the history table (purge, archival, whatever) you are going to interfere with the process which presumably polls the table.

My EUR 0.02

SF

>----- ------- Original Message ------- -----
>From: "Yechiel Adar" <adar76_at_inter.net.il>
>To: Multiple recipients of list ORACLE-L
><ORACLE-L_at_fatcity.com>
>Sent: Thu, 13 Nov 2003 03:14:25
>
>I usually use a switch in the scheduled job tables
>to indicate active
>status.
>After the job run jus NULL or put another value in
>it.
>If you put bitmap index on this the search will be
>a snap.
>
>There is no reason to hold this pointer in the jobs
>table.
>
>Yechiel Adar
>Mehish
>----- Original Message -----
>To: "Multiple recipients of list ORACLE-L"
><ORACLE-L_at_fatcity.com>
>Sent: Thursday, November 13, 2003 2:49 AM
>
>
>> 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
>>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriolecorp.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).