NEED HELP complicated problem with the design of a database

[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

[shanewaj]

Any Reply will be appreciated

[billnan]

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

I'd use a category department sequence table. Each entry in this table would be include the category, a sequential number, and the department that had that place in the sequence.

So, your table would have the following entries for category 1

(CAT1 - DEPT1 - DEPT2 - DEPT3 - DEPT8 - I'm assuming this is the sequence)

CAT1, 1, DEPT1
CAT1, 2, DEPT2
CAT1, 3, DEPT3
CAT1, 4, DEPT8

For category 2

CAT2 - DEPT1 - DEPT3 - DEPT5 - DEPT8

CAT2, 1, DEPT1
CAT2, 2, DEPT2
CAT2, 3, DEPT3
CAT2, 4, DEPT8

I hope this is clear.

Bill