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

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
>
> 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: Yechiel Adar
  INET: adar76_at_inter.net.il

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