This is my idea:
To create a base which would be used to link and organize things for the company, like: Production Machines, Workers (or Employees), Products, Stock, Customers, and Orders. In a way that I would have linked tables to organize relations between all of those.
All Machines can produce different kind of Products; different Workers can work on different Machines; only one Stock for all Products; different Customers can place Orders with different Products.
Would that be too complicated?
For example: Worker 1 worked today on Machine 1 and produces 100kg of Product 1 which would automatically increase the Stock of the Product 1 for 100kg. In the same time Customer 1 placed the Order 1 with 50kg of Product 1, so now the Stock of Product 1 went down for 50kg and all Workers which work on the Machines designed to produce Product 1 can continue producing Product 1 to fill up the Stock again. Something like that.
I think what you’re after is possible. You’ll need these tables:
- Production Machines
- Employee Log (or something similar, which will log each employees time on a machine)
- Order Line Items (if someone can order more than one product at a time, this table is necessary to log the quantity of each product ordered independently)
I think Stock can be handled from within the
[Products] table with some formula fields.