Originally Posted by
ArviLaanemets
A typical schema how such databases are set up:
You have a table where all parts of various types are defined - purchased, produced, fictitious, etc. tblParts: PartID, PartName, PartType, ....;
You have a table where workplaces/workstations are registered, etc. tblWorkstations: WSID, Workstation, ...;
You have a table where a production orders for certain part of produced type at certain date in certain workstation are registered, etc. tblProdOrders: ProdOrder, PODate, WSID, ProdID, Qty, ... (ProdID is PartID of produced part);
You have a table where for every part of produced type (products) a list of all component parts in assembly order are listed, with quantities needed at every step of producung a single unit of product, like tblBOM: BomID, ProdID, ProdBomRow, CompID, CompQty, ... (CompID is PartID of component assembled into product). This table allows you to calculate, which parts and how much of them are needed to fulfill the production order;
Probably you also need a table, where for every BOM list row, all operations needed to assemble the component into product are listed, like tblBOMOp, BomID, OpID, OpTime, ...;
Now we have prerequisites defined, and can proceed with actual workflow.
Starting with storage. Storage (a real, or imaginary) is a place, where all starts, and where all ends. Here you register all purchased, incoming from production, or otherwise gotten parts when they are coming in, and all sold, or scrapped, or bundling for production, or whatever parts when they are going out. Like tblStorageMoves: MoveID, [StorageID], PartID, MoveType, MoveDatetime, MoveQty, ...; This is the table, where you can always find out, how much you bought or sold certain part in certain period, or how much of certain parts are available currently, etc. You can have several storages, or a single one (in which case StorageID is optional field).
Another main table to make all this to work, is a table where finished products are registered. Like tblReports: ReportID, ProdOrder, RepDatetime, [ProdID], [QtyPlanned], QtyProduced, [QtyRemains], ... (in [] are optional fields - you can get according values from other tables, but sometimes it is more effective to calculate them and save).
Probably you'll need some additional tables e.g. to move info between production and storage, but essentially it is the gist of it.