| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: data modelling question - job vs. job history table
Jacques,
Why make it sooo complicated? I like the third table - Job_History that shows what job ran and when. Much clearer now, and in a year from now when you (or someone else) goes and reviews what you did.
Tom Mercadante
Oracle Certified Professional
-----Original Message-----
Sent: Wednesday, November 12, 2003 7:49 PM
To: Multiple recipients of list ORACLE-L
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).
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). Received on Thu Nov 13 2003 - 12:24:33 CST
 |
 |