Looking for advice on the best way to structure products/ subtasks. Basically we have a base with a lot of products that need to be created for clients. Each product has an overall due date, someone assigned to work on it, start date, priority, etc.
Sometimes these products are fairly simple meaning only one person will need to work on it; there are no subtasks, etc. Other times, the product is quite complex and needs to be broken down into subtasks with their own due dates, assigned workers, etc.
I'm trying to figure out the best way to structure this. Right now, all the info associated with the product (pricing, quantity, date start, due date, priority, assignee, etc) is in the same table, but this quickly gets confusing if a product has subtasks. What are my options here? Can I create an automation so that when a product is created, it also creates a linked task on a separate "task" table? And then if there are subtasks, those can be added manually on that "task" table and linked back to the product? We will want to ultimately build an interface that will allow someone to see ALL work assigned to them, regardless of if it's a "product" or a series of "subtasks". I feel like I'm missing something obvious here.
I have created and managed a repeatable make-to-order Manufacturing Execution System (MES) in Airtable, so most of what you want to do is possible. Unfortunately, Airtable is primarily for personal and IT use, and I don't have a template that could be used in a factory, so we don't have a suitable example.
A brief explanation of my MES. I have a table of production instructions, processes, process details, BOM and process templates. When an instruction is added to the production order, the process of the process template linked to that BOM is added process detail. The added process details are calculated backward from the quantity and delivery date to provide approximate scheduling. The process template has standard values such as main assignee and resources, which are copied to the process detail only at the beginning and can be edited.
The person in charge of the process can view his/her process list and report the results when the process is finished. The manager can check the progress of the process.