Manufacturing Entities and Relationships
Does anyone have an ERD mockup of a manufacturing database? I've started on one but as I go deeper I'm second guessing myself and wondering if I'm missing things.
This is a small place that wants to track materials used to build sub-assemblies that build a complete product.
Each sub assembly is made up of several sequences. Should I have a sequence table and should there be a joint table between the sequences and sub assemblies. I don't think there should be... because if I ask the question... One Sub Assembly is made up of several sequences.. but only one sequence is assigned to a particular sub assembly.. it seems I don't need a join table there.
Also the owner wants to track employee efficiency as they are making these sub assemblies. Each employee works on a sequence within the sub assembly and by days end the employee should be completing a certain number of these sequences. So the employee clocks in.. selects the sequence he's working on that day... as he completes a sequence, he presses a sequence complete button... and then begins working on another one.. and so on.. until EOD when he clocks out.. the database can calculate how many of those sequences he was able to complete.
My question is what would be the best way to tie these completed sequences into the sub assembly table.. or should not be... As sequences are completed which eventually creates a sub assembly.. that sub assembly needs to be added to the inventory.
attached is my preliminary drawing. I appreciate any advice.