How do I expand my many-to-many relationship?

[paturdc]

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.

[Ratslinger]

Hello,

It appears you can go multiple directions with this. Add a quantity on to tbl_BOM_Parts and list references there.

Another is to have a separate record fr each item in tbl_BOM_Parts table. This would require you to add an ID to the table.

Sample ------ Parts.odb

There is also a query to list items - needs some work for sequence and possibly ability to select a specific BOM.

[paturdc]

Thanks for the answer. Creating an ID would definitely work, it was the best solution I could come up with on my own. Keme’s answer however feels much more natural, since the references are really the unique parameter connected to the BOM.

p.

[keme]

The textbook case

The most common situation in a many-to-many relationship is perhaps where the two foreign keys of the link table constitute the (composite) primary key of that table. In this case, as far as I can tell, that is a wrong decision.

Real world

For your solution, each component is uniquely identified by which project it is a part of (tbl_BOM_Parts(BOM) → tbl_BOMs(ID)), and what component number (tbl_BOM_Parts(Reference)) it has in that project. Those are the two fields which should constitute the composite key of tbl_BOM_Parts.

This way you can have multiple entries for identical components in a project (which makes sense) and if you decide to adjust the spec of one component in your project, you just pick a different component from the storage (instead of removing reference from one list and inserting into another).

It also makes some sense to split the Reference field. A variable length string is perhaps not the most efficient target for indexing. Consider one single character field for component class (R/C/T/etc.) and a numeric for the sequence number within that class. This would also simplify sorting of long component lists (google “sort alpha vs numeric” for an elaboration). The downside is that this leaves no space for a suffix (“R9V” type of reference) unless you split the reference into 3 fields to add that specifier (like CompClass, CompNo, CompSpec).

[keme]

To arrive at a good structure for a complex database, you need the process of normalizing. Each step of this process brings a new “Normal Form”, or NF.

The operations of the process are based on each field’s nature (data type), role (descripton, magnitude, identity) and relationship to other fields (dependencies). You are the one who knows your data best. Leaving the normalizing to others requires very precise communication. Better learn the skill yourself!

3NF is considered essential for database integrity, and is a good foundation. Sometimes the basis changes (fields assume additional roles and dependencies are discovered, or you add new fields), so you should be constantly aware of the state of normalizing (the NF) for your db.

[paturdc]

Thanks for the reply. Your answer hits on the most important point, that I somehow didn’t realize until now: that the reference is the unique parameter, not the component.

Normalization was actually the reason I didn’t go forward with my previous approach, I couldn’t convince myself that my tables were not minimally redundant. Switching the key to reference rather than component solves that right out of the gate. I even believe it could all be in the same table, and still fulfill 3NF.