Design Question- allow schedule to match SN furthest in mfg proces

[MarkV]

I have a question about how to organize tables to create a manufacturing
schedule database. I've created a Status table which holds information about
where each part resides in the manufacturing process. I have a corresponding
Schedule table which has dates for when each part should finish each
manufacturing step. The link between the two is the PartID. I want to
decouple the two tables so that the schedule can be compared to the part
dynamically. That is, the schedule is not locked into a particular serial
number but allows whichever serial number is furthest along the process, to
be used to satisfy the schedule's requirement for that operation number.

The current table structures are similar to this:

tblStatus
StatID
PartID
WorkID
OpStart
OpStop

tblSched
SkedID
PartID
WorkID
DueDate

tblWorkInstruction
WorkID
PartID
OpNum
OpDescripton

tblPart
PartID
PartSerialNumber
PartDescription

 

[Paul Shapiro]

Your tblWorkInstruction is the production routing for a given partID? In
which case the combination of (PartID, OpNum) would be an alternative unique
identifier for tblWorkInstruction? If that's the case wouldn't parts of a
particular PartType move through production in the same order they enter
production? Or are there multiple workstations that can perform the same job
step?

If your schedule is based on Part, and not PartType, how can you
"substitute" one serial number for another? The schedule is for a particular
serial number? Could you revise the model:

Information about the products:
PartType (partTypeID, partTypeName, partTypeDescription...)
PK: partTypeID
JobStep (partTypeID, jobStepNumber, jobStepDescription, estimatedWorkHours,
....)
PK: partTypeID, jobStepNumber

Information about the individual parts:
Part (partTypeID, partSerialNumber, dateManufacturingCompleted, ...)
PK: partTypeID, partSerialNumber
PartSchedule (partTypeID, partSerialNumber, jobStepNumber, dueDate,
dateTimeStart, dateTimeEnd, ...)
PK: partTypeID, partSerialNumber, jobStepNumber
Note: If the schedule is by individual part, then the status can be
included in the schedule data.

If that data model would be applicable, you might redefine the problem as
swapping due dates if a later-scheduled part is ahead of an
earlier-scheduled part of the same PartType? You could generate a query to
identify qualifying parts for a swap. Compare the number of completed job
steps to the days remaining before the due date. If you sort the Parts in
production by partTypeID, jobStepNumber DESC, you would be looking for
out-of-order due dates. A grouping query based on that approach could
identify the parts to be rescheduled.