I am building a model for manufacturing aerospace systems for a small company...
NOTE- the Manufacture process is like...the parts for a given project will be designed by engineers, who produce their CAD. Some CAD designs are "assemblies" of lower-level parts...part A is made from 2 part Bs, 2 part Cs and some hardware (screws, nuts, etc). So, to that end, some assembly designs consist of not only the CAD and related draft file (drawing), but the draft/drawing will have a list of parts that define what all goes into making one unit of the assembly. This is where I'm getting lost in the design.
Our Engineers can export a .csv of the list of parts from the draft file. This could then be read into the database. Not sure if I need to consider each row of the .csv file as a record in the "PARTS" table or some other table, maybe "DESIGNED PARTS". There is a definite table needed called PARTS because I know I have part number, lot/date code, part description. This would be for off-the shelf, purchased parts like nuts, screws, washers, glue, etc. But I'm not sure if I need a "PARTS LIST" table. This might be for the fact that each designed assembly, the parts lists generated by the engineers (BOM, bill of materials) consist of both other parts they design and commercial-off-the-shelf parts. I'm thinking that there is enough of a difference between these two that they should each have their own table, else, I'll end up with lots of null values.
Entities/tables as I've identified so far:
Project (stores project-level records: pk_proj_id, proj_name, proj_start_date, fk_fab_ops_id, etc)
Fab/Ops (a parent record for storing the build history for a given part or sub-assembly)...stores pk_fabop_id, part description, part number, revision, date requested, fk_project_id
Fab/Ops Step (a collection of steps/operations that went in to building a part to completion) this will be the many side of a one to many relationship with Fab/Ops
Parts Lists (not sure if I really need this)...thinking a designed part has a part list, and only one part list. Unless it is revised, then the part gets a new revision level, and the part list grows or shrinks accordingly. It could be that I use "In-House Designed Parts" instead, and that way, each "list" will only be generated upon a search. The data would actually be just individual parts, but each would have an foreign key to a parent "Design" or "Drawing" number.
Part Assignments A collection of "transactions" where a part was "consumed" by a project via the Fab/Ops. This is a join table btwn a many-to-many involving EITHER the Fab/Ops table and the Parts table OR btwn the Part_Lists and the Parts table. Records date it was used, and gets you to the Fab/Ops parent.
Parts As you'd expect here. A table whose records track pk_part_id, Part Name, Part Number, Bin Number (storage location), Date/Lot Code, Qty on Hand (calculated), Description, Expiration Date. This table would be the One side of the 1:M relationship with Part Assignment.
Fab/Ops Step This table is a collection of "transactions". Like a Line_Item is to a Invoice. Think of the Fab/Ops as the Invoice, and the Fab/Ops Step as the Line Item. Captures what step was performed- full description, Employee Id of the technician (which is another necessary table), Date step performed, Calibrated Tools used (yet another related table). This is the Many side of a 1:M relationship with Fab/Ops.
Employee (see Fab/Ops Step, have not modeled this yet).
Calibrated Tools (see Fab/Ops Step, have not modeled this yet).
Please see uploaded image. Trying to get design ideas, considerations...