How to coordinate between different departments?

We have three depts: A, B and C. Each has some common needs to information like customer, salesperson but also have different needs like “tool purchased” or other data about the project that are exclusive to that dept.

We want to start an entry at the beginning of the new part process and move the “entry” through the pipeline in a flexible way. In other words Dept B might create the new entry, pass it to dept A. Dept A does some things and then passes it to Dept C. Sometimes it might have to go back to Dept B for some clean up.

Another way to see this is we want a digital object that holds information that can be moved from person to person, department to department, get changed, worked up, etc. Since we would have 1,000 items we need to limit what is displayed to just what that person needs. Only the data the person needed would be displayed (even though all the data still existed). When we were finished we would archive the data in some fashion.

We may have up to 1,000 different items in the works at any one time.

My question is what is the recommended way to:

Pass the entry from one dept to another so there is clear indication of who’s responsible for the task.

Limit the view for specific departments so that they can see just their items?

Limit the view for a person so that they could see just their items or tasks?

Page 1 / 1

Sounds to me like one way to model this is to have a PARTS table, a DEPARTMENTS table, a TASKS table, a CUSTOMERS table, a SALESPERSONS table, and a PROCESS join table.

There would be a one-to-many relationship between PARTS and CUSTOMERS, since a part can only have one customer, but a customer can have many parts.

There would be a one-to-many relationship between PARTS and SALESPERSONS, since a part can only have one salesperson, but a salesperson can have many parts.

There would be a many-to-many relationship between PARTS and DEPARTMENTS, since a part can be in many departments, and a department can have many parts. You would need a join table like PROCESS to link between PARTS and DEPARTMENTS.

There would be a one-to-many relationship between PROCESS and TASKS, since each process can only have one task, but one task can have many processes.

You then add a record in the PROCESS table with a Date field indicated when the process starts/ends, and a Department linked field to select the department, and a Task linked field to select the task, and a Quantity field to indicate how many (as a positive number) of that particular part came into the department.

You then add a new record in the PROCESS table indicating in the Date field when the process ended, and in the Department linked field the part is leaving from, and in the Quantity field (as a negative number) to indicate how many of that particular part left the department.

You then add a new record in the PROCESS table indicating in the Date field when the process starts, and in the Department linked field the part is entering to, and the Task linked field what is the task, and the Quantity field (as a positive number) how many of the particular part entered the department.

In this way you can show the various transactions of the various processes or tasks going in and out of each department.

You can run views or reports on the PROCESS table filtered by the desired salesperson, part, department, task, etc. to get the reports you need, rolled up by Quantity to see how many of each part are in each task in each department, etc.