Originally Posted by shanewaj

I am having a complicated problem with the design of a database.
Lets Describe the problem

This is going to be used in a job tracking system
we will have a list of jobs so
TABLE 1: JOBS (JOBID,DUE DATE, SHIP DATE, INVOICE DATE, STATUS, CATAGORY)

what is category well it tell what sort of job it is actually by that i have say this is a print job so this job should go to retouching department first then to printing then to dispatch
i have jobs that have to move between 8 departments.

So TABLE 2: DEPARTMENT (DEPTID, DEPT NAME, TIME REQUIRED FOR A JOB, NUMBER OF EMPLOYEE)

I have to keep track of all the jobs done by each department so a DEPARTMENT JOB RELATION which is many to many

AS TABLE 3: JOB_DEPT (JOBID,DEPTID)

NOW my problem is how to put category in action ...

as if i draw a table that might look like bellow

CAT1 - DEPT1 - DEPT2 - DEPT3 - DEPT8
CAT2 - DEPT1 - DEPT3 - DEPT5 - DEPT8
CAT3 - DEPT1 - DEPT2 - DEPT3 -DEPT3 - DEPT5- DEPT8
CAT4 - DEPT1 - DEPT2 - DEPT3 -DEPT4 - DEPT5-DEPT6 - DEPT7- DEPT8
CAT4 - DEPT1 - DEPT2 - DEPT4 -DEPT5 - DEPT3-DEPT6 - DEPT7- DEPT8
CAT5 - DEPT1 - DEPT7 - DEPT1 - DEPT2 - DEPT5-DEPT8
CAT6 - DEPT1 - DEPT6 - DEPT7 - DEPT4 - DEPT5-DEPT8

some thing like this.

what should i do ... just a reference table ...

I am also thinking of making the JOB_DEPT relation table in two parts 1 completed 2 uncompleted as by status field in JOBS Table will know where to go.

I am going in the right direction .... please reply .... with ideas and help ...

Thanks
Shanewaj