How to design a DB for production scheduling

[Vanita]

Hi,

Can anybody please explain how do i design a table/Form for scheduling a production plan? So this is how it goes.


1. As soon as the material is received, one has to plan it for production based on the availability of machines/shifts/dates.
2. The available machine/shifts/dates details can be fetched from a query which is designed for the same planning table for the details (dates/shifts/machine) which are already entered in the table.
3. But how do i make the unplanned machine/shifts/dates visible in query as available for planning?

Shall provide the other details if required.

[ranman256]

this is not a trivial app. So I cannot relay every bit of info needed without actually doing the work, but,

youll need tables for:
tMachines
tCalendar
tShifts
tSchedules
tEmployees?

tShedule table:
SchedID (autonum)
MachID (long) from tMachine table
SchDate (date/time)
Duration? (in hrs?)

tCalendar table:
ShdDate :

holds every date of every year for easy booking of machines by selecting from this list. Can be filled with a vb loop.

[orange]

I agree with ranman's list, but also think you will need something for Product and Materials.
That is it is machine and people availability to build X units of Product Y given that you have sufficient "raw material".
Here is a link to a general Process with Steps model from Barry Williams.
Here is a link to a MultiResource Scheduling model that may offer some insight.
Good luck with your project.

[Micron]

This is something that I wouldn't even try, unless your workflow is simplified. There can be TONS of variables that would throw a plan out of whack, such as
- material shortage
- machine break downs
- lack of machine operator
- tool/die changes that suddenly become necessary due to a schedule upset
- changes in production priorities
- order cancellations
- quantity changes
- qc issues introduce run shortages, maybe even material shortages, added labor time, additional die changes, etc.
- ad infinitum

There are off the shelf products that you might want to explore instead, and in spite some of them being designed by software engineers who presumably also understand the intricacies of scheduling, none of them really work in some manufacturing environments.

[orange]

Totally agree with ranman256 and micron re the complexity of such a business and any proposed database to support same.

@vanita
What exactly do you do now for such scheduling?

[Vanita]

Now its mannual, willing to design it in access.