I have a parts inventory for electronics. Every unique part is listed in a Components table. Whenever I need to do a project, I create a BOM (Bill of Materials). Each BOM contains several components from the tbl_Components table, and so I have a many-to-many relationship, that works perfectly:
| tbl_Components| uniquely identifies electronic components
| tbl_BOMS | lists the Bill of Materials for each individual project
| tbl_BOM_Parts | Junction table that lists which component belongs in which BOM (I should use component everywhere, but I’ve used Part a few places unfortunately. I’ll change it when I redesign everything.)
All of this works as intended, and I believe it is correctly setup. However I now wish to give each part on each BOM one or more reference designations. For example:
So I can have many references for each unique combination of BOM/MPN, and in my mind I imagine this as a many-to-many-TO-MANY relationship. But I believe that is not a real thing. I’ve tried adding the “reference” field to tbl_BOM_Parts, and this works if “Reference” is unique, but I feel like this way of doing it is not formally correct.
I’m a beginner, so I hope I’ve explained myself properly. Sorry for not using entirely consistent names, once i get it working well enough, I’ll redo everything.