We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

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

asked 2020-09-16 20:49:37 +0200

paturdc gravatar image

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.)

image description

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:

image description

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.

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2020-09-17 08:00:28 +0200

keme gravatar image

updated 2020-09-17 09:23:39 +0200

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).

edit flag offensive delete link more


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.

keme gravatar imagekeme ( 2020-09-17 16:54:59 +0200 )edit

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.

paturdc gravatar imagepaturdc ( 2020-09-17 23:13:28 +0200 )edit

answered 2020-09-17 04:34:36 +0200

Ratslinger gravatar image


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.

edit flag offensive delete link more


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.


paturdc gravatar imagepaturdc ( 2020-09-17 23:15:58 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-09-16 20:49:37 +0200

Seen: 99 times

Last updated: Sep 17 '20