Multiply Many-to-Many Relationships (1 Viewer)

khohense

New member
Local time
Today, 15:34
Joined
Mar 30, 2020
Messages
4
Hi All,

I'm fairly new to access, and I've run into an issue and I'm not sure the best way to proceed.

I have a many-to-many relationship between two tables (BillOfMaterials and PurchaseOrders) with a join table (PurchaseOrders_BillOfMaterials). I have a second table (FabBillOfMaterials) that also has a many-to-many relationship with the PurchaseOrders Table. Is there a way to use the same join table for both relationships (BOM to PO and FabBOM to PO) or is it best practice to make another join table?


1585781117000.png
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:34
Joined
Oct 29, 2018
Messages
21,358
Hi. You could try it, but it's probably simpler to separate them.
 

khohense

New member
Local time
Today, 15:34
Joined
Mar 30, 2020
Messages
4
Thanks for the quick reply!

Simpler is (almost) always better, so I will go with a separate join table and see how that works.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 17:34
Joined
Feb 28, 2001
Messages
27,001
Is there a way to use the same join table for both relationships (BOM to PO and FabBOM to PO) or is it best practice to make another join table?

Actually, you run a serious risk if you create the 2nd junction table AND you run a serious risk if you try to adapt the first of your junction tables to become a junction to a third table. This is on theoretical grounds so I have to be careful in the explanation.

First: If you created a junction table between FabBOM to PO and also had a junction between BOM to PO, you ALSO have a path between BOM and FabBOM. This would leave you with two distinct paths from BOM to PO that passed through totally different intervening tables. You MIGHT be able to work with this but the Access built-in wizards would barf when they saw that dual path. There might be a way to build everything by hand but you would have to fully qualify any linkage paths because the MOMENT you don't, you have an ambiguous path. Access won't know how to correctly build JOIN queries in that part of your structure.

Second: If you tried to merge the function of the 2nd junction table into that of the first junction table, in the final analysis you would have an ambiguity. On the PO side of the junction, both cases point to a PO record. No problem there. But the other side EITHER points to the BOM table or the FabBOM table. You couldn't use the same field on the BOM/FabBOM side for both links, particularly since the links potentially overlap anyway due to that relationship between BOM and FabBOM. And if you have two fields, only one of which applies at a time, you run into issues when you try to run ANY query or ANY report or ANY form on a JOIN of the junction table to either BOM or FabBOM - because what it will look like to Access is that some members of your junction table have no junction. If this sounds confusing, you are right - and that is how Access will see it.

This type of problem is generally indicative of a structural or design error. Can you discuss the purposes of the BOM and FabBOM tables? Is it possible that one is a child of the other? Because the crux of the matter is that relationship between the two.
 

khohense

New member
Local time
Today, 15:34
Joined
Mar 30, 2020
Messages
4
What I'm trying to do is improve our workflow from dozens of excel spreadsheets into a relational database, while trying to reduce changes to other processes.

The primary purpose of the database is to store purchase orders.
  • The BOM is a list of parts that need to be purchased. Each part in the BOM has a 6 digit serial number and may need more than one purchase order i.e. one to purchase the part and one to ship it.
  • The fabBOM holds details of fabricated parts within the BOM and also may require multiple PO's (i.e. Material, labour and paint all from separate vendors) For example, a kitchen table would be listed as a single part number on the BOM, but in reality we need to purchase a table top, 4 legs and glue to hold it together. These item are stored in the fabBOM, and are identified by the 6 digit serial number dash the item number within that part. For example:
    • 012345: Table
      • 012345-1 Table top
      • 012345-2 Legs
      • 012345-3 Glue
  • The purchase orders are lists of parts that are purchased from the same vendor. For example, bolts, nuts and washers will be listed on the BOM, and weld nuts will be listed on the fabBOM, but will be purchased from the same vendor on the same PO.
I am aware this isn't the best way to track part numbers, but unfortunately we are restricted by our 3d modeling software and part number generator (SolidWorks and SolidWorks PDM). As well, we have years worth of data (10s of thousands of serial numbers) already in this format.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 17:34
Joined
Feb 28, 2001
Messages
27,001
The usual solution to this problem is simple to say (though harder to do). I am not going to claim personal expertise but I have seen solutions for cases where what you had was called an "Assembly" and you would buy that, but you could ALSO buy parts independent of the assembly. You can try to do a forum Search for "Parts Assembly"

Here's the general idea. You collapse the BOM and FabBOM into a single table. It is almost immaterial whether you have an extra column that says "I am an assembly/fabrication" vs "I am an elementary part." Then you create a special kind of junction table called a "self-junction" table - which means that if you need to define an assembly / fabrication as a group of parts, you create a list of parts in the self-junction.

How this works (being arbitrary here and picking sides): the left side of one entry in this self-junction points to the assembly / fabrication entry. The right side of one entry in the self-junction points to one of the components used in the construction of the complex item. In other words the self-junction table just lets you associate elementary parts with their complex product in the same table. And that means you can sell fabrications OR you can sell spare parts individually.

What this buys you is that NOW you can have a SINGLE junction between the BOM table and the PO table. You just aren't very likely to purchase items marked as a fabrication (unless you purchase them from yourself). But that doesn't invalidate the table at all.

Hope I didn't lose you with that.
 

khohense

New member
Local time
Today, 15:34
Joined
Mar 30, 2020
Messages
4
I think I follow. The self-junction table only needs two fields, a parent and child, which are both pulled from the inclusive BOM table. And being able to easily apply a PO to the fabricated "assembly" is important since labour is typically applied as a single PO for the assembly of the fabricated part.

I like this approach since it could allow for multiple stages. To pull my example from above, we could have a table set that included a table and chairs.

My main concern now is importing our data into the database to maintain our structure. But I will do some searching now that I have an idea of the table structure.

I appreciate all the help!
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 17:34
Joined
Feb 28, 2001
Messages
27,001
Not a problem. It is why we come here.

Just a clarification, though. The self-junction needs one more field, I think... the number of components that are the same. I.e. a table doesn't need a 012345-2 Leg - it needs 4 of them. But you would probably have figured that out quickly.

But I will do some searching now that I have an idea of the table structure.

Absolutely. Just remember, design of a database is an iterative process. You'll do a little, research a little, do a little more, ... perfectly normal and definitely something to be expected. Don't let a brief pause for more research act like a stumbling block. It is not a stumble. Good luck on that search process.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 22:34
Joined
Jul 9, 2003
Messages
16,245
database is an iterative process.

I hadn't realised the significance of the iterative process. That's why it's difficult to answer some people's questions, you don't know where they are in the process.
 
Last edited:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 17:34
Joined
Feb 28, 2001
Messages
27,001
Uncle G, there are times when I can see an entire design in my head, and then there are times when it is "through a glass, darkly." Sometimes you have to pick and feel your way through, particularly if you take a "divide and conquer" approach - which I do.

In the world of developing for a purpose, you sometimes have to address basics and then, when the basics are correct, come back to add more features, which might require some redesign. It is NEVER quick when your problem is non-trivial. Add to that the simple fact that if your boss asked you to do something, he might see what a bang-up job you did and ask you to add something else. Whoops! There goes the finished design.

We must remember that real-life projects for real-life businesses are created for real-life people who, being human, don't think of everything at once. As long as a product is being actively used, you can expect the need for change which means you can expect to revisit your designs now and then.

Sorry for the slight diversion, khohense.
 

Users who are viewing this thread

Top Bottom