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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Mercadante, Thomas F INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
