Support for Approved Manufacturer Lists (AML)

[cdwilson]

Hi, I just got InvenTree up and running for the first time, and it's awesome!

I'm trying to figure out if Approved Manufacturer Lists (AML) (also known as Approved Vendor Lists (AVL)) are supported?

For example, we create top-level "house parts" which are assigned an Internal Part Number. For example:

Part Name: CAP CER 0.1UF 16V X7R 0402, Internal Part Number: FCD-10014-01

Under that single Internal Part Number, we maintain a list of "approved MPNs", where any of the MPNs on this list can be selected by the contract manufacturer for the assembly of the PCBA:

IPN: FCD-10014-01

• Yageo CC0402KPX7R7BB104
• Murata Electronics GCM155R71C104KA55J
• KEMET C0402C104K4RACTU

Furthermore, each of the MPNs in the AML can have a list of multiple approved supplier/vendor SKUs:

MPN: Yageo CC0402KPX7R7BB104

• Digi-Key 311-3559-2-ND
• Mouser 603-CC402KPX7R7BB104

I think I've been able to create something like this relationship by adding multiple supplier part numbers, but I have to duplicate the MPN information for each supplier part. Ideally, I'd like to be able to create the MPN once (and manage it on it's own page with photo, datasheet, URL, etc), and then assign multiple supplier parts under that single MPN.

Internal P/N -> Manufacturer P/N(s) -> Supplier P/N(s)

I'm curious if there is another way to implement this in InvenTree currently or if you'd be open to adding support for managing MPNs independently from Supplier P/Ns.

[SchrodingersGat]

hey @cdwilson glad you're liking InvenTree!

It sounds like you are using the Supplier Part feature pretty much how I intended it. The ability to specify MPN / SKU separately allows some flexibility for various use cases.

Perhaps an option would be to provide a "duplicate" function for Supplier Parts - that way the MPN / SKU fields are pre-populated and save some time?

[cdwilson]

The ability to specify MPN / SKU separately allows some flexibility for various use cases.

@SchrodingersGat totally agree that being able to specify MPN separately from SKU is desirable (I'm actually advocating for decoupling these further).

As I understand it, in the current InvenTree implementation, the data model looks like this, where the MFR/MPN are just additional fields on the SKU:

Part (IPN)
├── SKU #1 (MPN #1)
├── SKU #2 (MPN #2)
├── SKU #3 (MPN #3)
├── SKU #4 (MPN #4)
└── etc...

Imagine if you wanted to add the following new fields to the data model:

• MPN Datasheet (file upload)
• MPN URL (string)

It doesn't make sense to add them to the Part model, because each MPN has a different Datasheet and URL value. If you were to add these fields to the SKU, you would have to duplicate them for each SKU. Even worse, if you wanted to update the Datasheet file or the URL for an MPN, you would have to go through and update these for each SKU on the part.

Now imagine if instead, the MPN was modeled separately from the SKU:

Part (IPN)
├── MPN #1
|   └── SKU #1
|   └── SKU #2
├── MPN #2
|   └── SKU #1
|   └── SKU #2
└── etc...

You could add the Datasheet and URL fields to the MPN, and maintain a list of supplier SKUs under that MPN. Each time the Datasheet or the URL changes, it only needs to be changed once on the MPN. Furthermore, this allows you to have things like a URL field on the MPN (for the manufacturers product page), and a separate URL on each supplier SKU (for the distributor's page).

Perhaps an option would be to provide a "duplicate" function for Supplier Parts - that way the MPN / SKU fields are pre-populated and save some time?

In my mind, it's less about "saving time" and more about structuring the data so that there is always a single source of truth (no duplication of data that can get out of sync). In the current model, the MPN data is duplicated across multiple SKU records because it's not possible to manage the MPN as a first class object.

What I've described above is how I've seen this problem approached in multiple PLM systems I've used in the past. While I think it has a lot of advantages, I'm definitely new to InvenTree and open to learning other ways of thinking about this. Curious to get your thoughts on the example I described above.

[eeintech]

@cdwilson I totally agree with your approach, which I've used in my previous companies and PLM systems.

Now with InvenTree, I did think of the same initially, that MPN and SKU were in reverse order in the tree. But it actually did not bother me and my colleagues much because we are essentially using the MPN to source our parts, we only leave that information in our BOM and leave it up to the assembler to buy it from its preferred source/supplier.

That flat approach (SKU and MPN one-to-one relationship) works pretty well for purchase orders too. It could add a bit of complexity on the purchasing side, if that relationship is altered.

Long-term I would be on-board with your idea too, it just makes a lot of sense.

[cdwilson]

That flat approach (SKU and MPN one-to-one relationship) works pretty well for purchase orders too. It could add a bit of complexity on the purchasing side, if that relationship is altered.

@eeintech Do you mean more complexity in the InvenTree UI when adding a new line item to a PO? When I create a PO in InvenTree and add a line item, I can choose from multiple approved Supplier SKUs for the same IPN:

I don't think changing the MPN-to-SKU relationship would add more complexity to this process, i.e. you would have exactly the choices as shown in the screenshot above, except the fields would have a different order with MPN first and then SKU second:

| IPN | Manufacturer | MPN | Supplier | SKU |

In the end, you would still be choosing a specific SKU to add to the PO.

I'd be really interested to understand a bit more from your experience about why it adds more complexity on the purchasing side. Could you explain in a bit more detail?

[eeintech]

@cdwilson Sorry did not mean to be alarming about this. Yes it is just complexity on the application because you essentially decouple one data set into two. But that's certainly doable.

The main issues I can see is that while building the purchase order, a user may want to add a bunch of MPNs to the list of parts to order. Then when going through with the supplier order, some of those MPNs are not carried by the supplier. So my guess to prevent this would be to be able to add only parts which have supply data with that supplier you're ordering from. Which essentially comes back to why do you need to decouple data between manufacturer and supplier instead of keeping it joint?

[cdwilson]

The main issues I can see is that while building the purchase order, a user may want to add a bunch of MPNs to the list of parts to order. Then when going through with the supplier order, some of those MPNs are not carried by the supplier. So my guess to prevent this would be to be able to add only parts which have supply data with that supplier you're ordering from.

Yeah, in the context of creating a PO, that's how I would imagine this would work. When you add a line item to a PO in InvenTree today, it already filters down the list of parts to only show those available from the chosen supplier. I don't see why this behavior would need to change if the MPN was managed independently from the SKU.

Which essentially comes back to why do you need to decouple data between manufacturer and supplier instead of keeping it joint?

The process of creating a PO revolves around choosing specific SKUs at a specific supplier. If the only function of InvenTree was creating supplier POs, I think you are right, there is not much value in decoupling the MPN from the SKU.

However, in some contexts, being able to refer to an MPN without referring to a specific SKU is helpful. A simple example is being able to link to a specific MPN URL in InvenTree. Today, you can link to a supplier part directly, but you can't link directly to an MPN.

Another example is exporting an assembly BOM that contains MPNs, but does not contain supplier SKUs. As far as I can tell, this is not supported in InvenTree today (the only options are to export with SKU+MPN or without SKU+MPN).

A third example is when you want to create parts that have an MPN, but do not have a SKU. Some examples are customer-specific MPNs which are not stocked by distributors, or pre-release silicon which was obtained from a manufacturer directly. In these cases, you would want to create an MPN so you could add the part to the assembly BOM, but you may not have a supplier SKU. You could work around this in InvenTree by creating a "SKU" where the SKU is just the MPN, but that feels more like a workaround to me.

That said, the biggest value I see in being able to manage the MPN separately is what I mentioned in the previous post, i.e. having a single source of truth for each MPN. When I'm adding the same MPN to multiple supplier parts, it's easy to make a typo on one of those supplier parts but not the other ones. Also, if InvenTree ever adds additional MPN-specific features (datasheets, manufacturer URLs, etc), it would be desirable to not have to specify/edit those values across multiple supplier parts.

[eeintech]

I don't see why this behavior would need to change if the MPN was managed independently from the SKU.

No it wouldn't change much on user front-end but the logic behind would be different.

A third example is when you want to create parts that have an MPN, but do not have a SKU.

You must be aware that it is possible for a company to be both Manufacturer and Supplier. In InvenTree this is enabled by checking both boxes. The SKU can be the MPN in this case, it is therefore possible to have "Supplier" part just being manufacturer parts and all your example fulfilled with the current implementation. Although it is not made quite clear in the naming.

Also, if InvenTree ever adds additional MPN-specific features (datasheets, manufacturer URLs, etc), it would be desirable to not have to specify/edit those values across multiple supplier parts.

Thanks for pointing this out, to me this is the winning reason why to decouple MPN and SKU and make SKUs "children" of a MPN. I did have to duplicate information and the process is quite manual right now. If the MPN became the parent, all SKU would point out to the same information and no need for duplicates!

@SchrodingersGat What do you think? I might have time on my end to give it a shot

[cdwilson]

No it wouldn't change much on user front-end but the logic behind would be different.

Yeah, I recognize this isn't a trivial change... just wanted to throw out the idea and see what you folks thought :)

You must be aware that it is possible for a company to be both Manufacturer and Supplier. In InvenTree this is enabled by checking both boxes. The SKU can be the MPN in this case, it is therefore possible to have "Supplier" part just being manufacturer parts and all your example fulfilled with the current implementation.

I tried this initially. This works in the case where the Manufacturer = Supplier. However, this does not work as well for the case when you want to purchase the same MPN both directly from the Manufacturer and from other Suppliers.

For example, let's say that I want to buy TE Connectivity AMP Connectors 1734328-2 from Digi-Key for a quick prototype run, but for cost optimization, I want to purchase large quantities directly from TE for a production build. As you suggested, you could add two different supplier parts, one for Digi-Key, and another for TE (shown in the image below):

If I create a PO for Digi-Key, the Supplier part for Digi-key correctly shows up in the filtered list of parts:

However, if I try to create a PO for TE directly, I would expect to see all MPNs which have Manufacturer set to TE Connectivity AMP Connectors. Instead, the list only shows Supplier parts which have TE Connectivity AMP Connectors as the Supplier.

This means that any part which is purchased both directly from the manufacturer and from distributors must be added as two different "supplier parts", causing the MFR/MPN to get manually duplicated 3 times in this example:

On the other hand, if you had the ability to structure the Digi-Key SKU as a child of the TE MPN, you would only need to enter each of them once. Any purchase orders sent to TE could filter on the list of TE MPNs (independent of Supplier), and any purchase orders sent to Digi-Key could show a list of Digi-Key SKUs.

[eeintech]

@cdwilson I have to admit this is a really convincing example!

I may be able to give it a shot next week, I started looking into it today but got busy with other things

[eeintech]

Started to look into this but haven't done any step forward yet.

As I mentioned earlier, it create a change in the database structure. Either option could work:

1. SupplierPart becomes ManufacturerPart and SupplierPart gets ManufacturerPart as ForeignKey and a handful of other fields, we can trim the supplier data from the ManufacturerPart model
2. Add ManufacturerPart as middle-man between Part and SupplierPart model, and trim fields from the SupplierPart model which will be going to the ManufacturerPart model instead

In term of data migration I think option 1 is best, as the "Supplier" table only changes name an retains most of its data, with some of it going to a new table (the supplier data only).

As this is an relatively heavy change, I hope @SchrodingersGat can chime in with his own experience of database restructuring.

[SchrodingersGat]

I have a couple of concerns, relatively minor. Mostly related to the added layer of "complexity" required to capture data.

1. How do we manage "deletion" of a ManufacturerPart? Do all the SupplierParts which reference it also get deleted?
2. What happens in the case where we can purchase a part directly from the manufacturer, and thus it is both a ManufacturerPart and a SupplierPart?
3. What is the process for adding the new data?
4. Is the process of placing purchase orders unchanged?

I am cautious to proceed quickly on this, I want to make sure we are fully aware of how the changes to the models will flow-on to other parts of the system.

The data migration itself should not be too difficult to manage.

[eeintech]

1. How do we manage "deletion" of a ManufacturerPart? Do all the SupplierParts which reference it also get deleted?

I would believe so. A supplier part has no reason to exist without a manufacturer part. In the tree, SupplierPart is a child of ManufacturerPart.

2. What happens in the case where we can purchase a part directly from the manufacturer, and thus it is both a ManufacturerPart and a SupplierPart?

In this case the manufacturer part information would be directly used for BOMs and orders, because all them would point to a ManufacturerPart and no more a SupplierPart. SupplierPart is extra and optional whereas ManufacturerPart becomes mandatory. The BOM export will have the option to include either manufacturer data or supplier data (which can be blank), or both.

3. What is the process for adding the new data?

To add a manufacturer part, it would be the same form as the current "Create new Supplier Part" form, except that we would strip away the Supplier and SKU fields. Manufacturer and MPN fields would become mandatory.

To add a supplier part, user would have to navigate to the ManufacturerPart detail page, click on a new tab called "Suppliers" (similar as the current tab on the Part detail page) and add supplier parts from there. The form would be very simple with the two Supplier and SKU fields.

4. Is the process of placing purchase orders unchanged?

Could be unchanged, and supplier parts for the chosen supplier need to be in the database (the information from the parent manufacturer parts wouldn't be used for this process).

Or we could also add the ability to order from a manufacturer company directly, not only suppliers. In that case, the data from the ManufacturerPart is used for the order.

What do you guys think?

[cdwilson]

I am cautious to proceed quickly on this, I want to make sure we are fully aware of how the changes to the models will flow-on to other parts of the system.

Definitely agree.

1. How do we manage "deletion" of a ManufacturerPart? Do all the SupplierParts which reference it also get deleted?

I would believe so. A supplier part has no reason to exist without a manufacturer part. In the tree, SupplierPart is a child of ManufacturerPart.

I think this is the correct behavior. Is there ever a case where two ManufacturerParts will need to reference the same SupplierPart? I can't think of a real-world example where this would make sense, and even if it does exist, it's probably not a big deal to duplicate the supplier info in those edge cases rather than introduce added complexity to deal with that case.

2. What happens in the case where we can purchase a part directly from the manufacturer, and thus it is both a ManufacturerPart and a SupplierPart?

In this case the manufacturer part information would be directly used for BOMs and orders, because all them would point to a ManufacturerPart and no more a SupplierPart. SupplierPart is extra and optional whereas ManufacturerPart becomes mandatory. The BOM export will have the option to include either manufacturer data or supplier data (which can be blank), or both.

This is how I was thinking it would work as well.

3. What is the process for adding the new data?

To add a manufacturer part, it would be the same form as the current "Create new Supplier Part" form, except that we would strip away the Supplier and SKU fields. Manufacturer and MPN fields would become mandatory.

To add a supplier part, user would have to navigate to the ManufacturerPart detail page, click on a new tab called "Suppliers" (similar as the current tab on the Part detail page) and add supplier parts from there. The form would be very simple with the two Supplier and SKU fields.

Agreed. In addition to the Supplier and SKU fields, I think you would want to have a Supplier link for the supplier's SKU URL (this is what I put into the SupplierPart link today). You would probably want separate links on the MPN itself for a link to the datasheet, manufacturer part page URL, etc.

4. Is the process of placing purchase orders unchanged?

Could be unchanged, and supplier parts for the chosen supplier need to be in the database (the information from the parent manufacturer parts wouldn't be used for this process).

Or we could also add the ability to order from a manufacturer company directly, not only suppliers. In that case, the data from the ManufacturerPart is used for the order.

Ideally you should be able to place a PO against a Manufacturer (with MPNs) or a Supplier (with SKUs). I'd vote for the 2nd option in this case.

[rco3]

For what it's worth, I love this initiative. It feels closer to how I see the part|MFG|Supplier relationship, and how I track parts.

Aside from inelegance, I don't see any reason that a SupplierPart, as a child of a ManufacturerPart, couldn't duplicate the MPN as SKU and Manufacturer as Supplier. This covers both a) the case of buying LSJ689s from NACSemi (distributor) vs. direct from Linear Systems (mfg), and b) the case of buying #6-32 screws from McMaster-Carr where the distributor SKU is the only reference number you have and you never know the manufacturer. In the former case, you would have two SKUs, one from a distributor and one from the MFG. In the case of McMaster parts, you would have one SupplierPart SKU and the MPN would mirror that.

I think the case where SKU is known but MPN is a) unknown or b) multiple (McMaster) is going to be by far less common than SKU having a 1:1 correspondence with MPN. But any given MPN is likely to have multiple SKUs - sometimes from the same vendor! I can buy MMBT3904's from Digi-Key under three different SKUs, but it's the same MPN - this is a strong example of why I think this is a move in the right direction. Kudos, @cdwilson , and thanks again to @eeintech and @SchrodingersGat.

[cdwilson]

the case of buying #6-32 screws from McMaster-Carr where the distributor SKU is the only reference number you have and you never know the manufacturer

@rco3 I'm glad you mentioned this example. I feel like there are probably other cases like this that we should try to think through.

I'm gonna propose an idea below which I think might be able to address the cases mentioned by @rco3.

In InvenTree, the current part "hierarchy" is is something like the following:

SupplierPart_A [Supplier=Digi-Key, SKU=490-4759-1-ND, Manufacturer=Murata, MPN=GCM155R71C104KA55D]
SupplierPart_B [Supplier=Mouser, SKU=81-GCM155R71C104KA5D, Manufacturer=Murata, MPN=GCM155R71C104KA55D]
SupplierPart_C [Supplier=Mouser, SKU=81-GCM155R71C104KA5J, Manufacturer=Murata, MPN=GCM155R71C104KA55J]

Digi-Key
└── SupplierPart_A

Mouser
├── SupplierPart_B
└── SupplierPart_C

Murata
├── SupplierPart_A
├── SupplierPart_B
└── SupplierPart_C

MyPart_1 [IPN=CAP-12345-01]
├── SupplierPart_A
├── SupplierPart_B
└── SupplierPart_C

If we add a ManufacturerPart into the model as proposed, we would end up with a relationship like the following:

SupplierPart_A [Supplier=Digi-Key, SKU=490-4759-1-ND]
SupplierPart_B [Supplier=Mouser, SKU=81-GCM155R71C104KA5D]
SupplierPart_C [Supplier=Mouser, SKU=81-GCM155R71C104KA5J]

Digi-Key
└── SupplierPart_A

Mouser
├── SupplierPart_B
└── SupplierPart_C

ManufacturerPart_X [Manufacturer=Murata, MPN=GCM155R71C104KA55D]
├── SupplierPart_A
└── SupplierPart_B

ManufacturerPart_Y [Manufacturer=Murata, MPN=GCM155R71C104KA55J]
└── SupplierPart_C

Murata
├── ManufacturerPart_X
└── ManufacturerPart_Y

MyPart_1 [IPN=CAP-12345-01]
├── ManufacturerPart_X
└── ManufacturerPart_Y

To support the case that @rco3 mentioned (where there is no Manufacturer/MPN), it seems like you would want the ability for SupplierPart to reference (ForeignKey) either a Part OR a ManufacturerPart. This would allow ManufacturerParts and SupplierParts to be listed on the "Suppliers" page for a Part.

SupplierPart_D [Supplier=McMaster, SKU=91251A268]
SupplierPart_E [Supplier=McMaster, SKU=91864A085]

ManufacturerPart_Z [Manufacturer=NutzNBolts, MPN=0101015]
└── SupplierPart_E

MyPart_2 [IPN=SCR-98765-01]
├── ManufacturerPart_Z
└── SupplierPart_D

To revisit @SchrodingersGat questions assuming it's possible to have both ManufacturerPart and SupplierPart listed under the "Suppliers" page for a Part:

1. How do we manage "deletion" of a ManufacturerPart? Do all the SupplierParts which reference it also get deleted?

When a ManufacturerPart is deleted, any SupplierPart which references the ManufacturerPart would be deleted.

When a Part is deleted, any SupplierPart which references the Part would be deleted. (When a Part is deleted, I don't think we want to cascade delete any ManufacturerPart since the same ManufacturerPart may be added to multiple Parts)

2. What happens in the case where we can purchase a part directly from the manufacturer, and thus it is both a ManufacturerPart and a SupplierPart?

In the case where SKU is not available, and you can only purchase from a Manufacturer, you would represent that as shown below with ManufacturerPart_W.

In the case where you can purchase an MPN directly from the Manufacturer OR from a Supplier via SKU, you would represent that as shown below with ManufacturerPart_Z & SupplierPart_E.

In the case where an MPN is not available, and you can only purchase a SKU from a Supplier, you would represent that as shown below with SupplierPart_D.

MyPart_2 [IPN=SCR-98765-01]
├── ManufacturerPart_W
├── ManufacturerPart_Z
│   └── SupplierPart_E
└── SupplierPart_D

3. What is the process for adding the new data?

Under the "Suppliers" page on a Part, you would have two buttons:

• "+ New Manufacturer Part"
• "+ New Supplier Part"

The "+ New Manufacturer Part" modal would be as @eeintech described above.

The "+ New Supplier Part" modal would be identical to the "+ New Supplier Part" button inside the ManufacturerPart Suppliers page, except that it would have an additional optional dropdown to select a ManufacturerPart. If the user does not choose a ManufacturerPart, the new SupplierPart would be added directly to the Part. However, if the user chose a ManufacturerPart from the dropdown, the SupplierPart would be added as a child of the selected ManufacturerPart (effectively as a shortcut for adding SupplierParts directly from the Part Suppliers page).

4. Is the process of placing purchase orders unchanged?

I think the PO would allow you to purchase any valid MPN or SKU for the Company the PO is issued to. If a Company is a Manufacturer, you would be allowed to add any MPN manufactured by that Manufacturer. If a Company is a Supplier, you would be allowed to add any SKU carried by that Supplier. If a Company is listed as both a Manufacturer and a Supplier, you should be able to add any MPN OR SKU for that Company to the PO.

Would something like this be possible?

[eeintech]

@cdwilson Yes, it is possible for a SupplierPart to be linked to either a Part or ManufacturerPart.

The Owner model, used to define the owner of a stock location or item, is based on the Django's content type framework:

https://github.com/inventree/InvenTree/blob/57289fe1415bd256beceb4dacc3e3791b897ebf0/InvenTree/users/models.py#L428

It can refer to either a User or Group instance, which are two different models, using the type and id of the instance.

So the new SupplierPart class could be built upon the same idea and implementation, with Part and ManufacturerPart being the two models allowed as GenericForeignKey.

I like your approach @cdwilson because it makes it really flexible, therefore will cover much more use-cases. The logic behind could get a bit more messy, but I'm sure we can get there.

[eeintech]

One detail I just thought of is the migration phase. It is critical to adapt the current databases to this new system.

My initial idea is to process the "old" SupplierPart instance and data according to the current database data:

• If only the Supplier/SKU data is present (no Man/MPN), reformat and link the SupplierPart to the Part instance directly
• If both Supplier/SKU and Man/MPN data are present, reformat and link the SupplierPart to a new ManufacturerPart, then link the ManufacturerPart to the Part instance.

Does it make sense?

[cdwilson]

This makes sense to me. However, I think there are some corner cases that should be considered:

It is possible that an "old" SupplierPart has a Manufacturer but no MPN value, or an MPN but no Manufacturer. I'm not quite sure what to do in those cases. It seems like you need both a Manufacturer & MPN to be present to automatically create a new ManufacturerPart.

[eeintech]

If data exist for either Manufacturer or MPN, a new ManufacturerPart shall be created and data populated accordingly. The two fields will become mandatory if the ManufacturerPart is edited, through the edit form. Otherwise it will stay as-is.

[cdwilson]

Ahh, I see. If I'm understanding you correctly, in these types of cases, you might end up with multiple ManufacturerPart instances with the same Manufacturer and no MPN, but each of these ManufacturerPart will be a different instance. Then, after the migration is done, the user can go back and edit these instances individually to add the MPN details if they want. That makes sense to me.

[eeintech]

@cdwilson That's right, the migration system cannot make decision for the user to merge data that was incorrectly setup 😉

[eeintech]

Do we all agree the update to the tree structure should be:

Does this cover all scenarios?

[rco3]

I think that's dead on, @eeintech.

[cdwilson]

Does this cover all scenarios?

Yes, I think this covers all the scenarios we've discussed so far.

There is one scenario which is not covered in the diagram above, and that is the case where the same SupplierPart is added to multiple Parts:

SupplierPart_X [Supplier=McMaster, SKU=91251A268]

MyPart_1 [IPN=SCR-12345]
└── SupplierPart_X

MyPart_2 [IPN=SCR-98765]
└── SupplierPart_X

In a previous message above, I proposed:

When a Part is deleted, any SupplierPart which references the Part would be deleted.

If a single SupplierPart can be added to multiple Parts, we would need to ensure that the SupplierPart is only deleted if it's not referenced by any other Parts.

[eeintech]

@cdwilson Good point, I'll have to add this to the test cases.

[eeintech]

To clarify, it would actually be:

Dataset A = [Supplier=McMaster, SKU=91251A268]

MyPart_1 [IPN=SCR-12345]
└── SupplierPart_X with Dataset A

MyPart_2 [IPN=SCR-98765]
└── SupplierPart_Y with Dataset A

Like it is right now. You can create a new SupplierPart containing the same information as another but the dataset and relationship to the parent Part should be unique. And I think we want to keep this unique relationship between Part and ManufacturerPart or SupplierPart for the new system as well.

Therefore I believe the correct system update/migration diagram:

And yes it sucks that there is a "duplicate" instance of a ManufacturerPart for the same dataset... but it shouldn't have been there in the first place if undesired.

Now what this new system will be allowing is this:

And I think this what we're all after!

[cdwilson]

And yes it sucks that there is a "duplicate" instance of a ManufacturerPart for the same dataset... but it shouldn't have been there in the first place if undesired.

I'm not sure this is desirable. One of the main benefits of having the ManufacturerPart be a separate model from the SupplierPart is having a single source of truth for each of these types. If I need to update the Manufacturer datasheet for an MPN, I only want to update it on one ManufacturerPart instance in the system. If I update the Supplier URL for an SKU, I only want to update it on one SupplierPart instance in the system.

Here's how I've been imagining this relationship to look:

I think some of the complexity of this data model has to do with how deletes are handled. In the current data model, deletes are simple because a single SupplierPart can't be linked to multiple Parts: when you delete a Part, all linked SupplierParts are cascade deleted.

In the new proposed model in my diagram above, there are 3 cases you have to consider for deletes:

1. Deleting a SupplierPart
2. Deleting a ManufacturerPart
3. Deleting a Part

Deleting a SupplierPart

This case is simple since there are no cascade deletes: you just delete the SupplierPart.

Deleting a ManufacturerPart

In my diagram above, I'm making two assumptions:

• It doesn't make sense for a single SupplierPart to ever be linked to multiple ManufacturerParts
• It doesn't make sense for a single SupplierPart to ever be linked to a Part and a ManufacturerPart simultaneously

If these assumptions are true, then deleting a ManufacturerPart is straightforward: when you delete a ManufacturerPart, any linked SupplierParts are only deleted if they are not linked to any other ManufacturerPart or Part. Since this is always true given the assumptions above, whenever a ManufacturerPart is deleted, all child SupplierParts will be deleted as well.

Deleting a Part

Parts can have ManufacturerParts and SupplierParts linked to them.

If we allow a SupplierPart to be linked to multiple Parts in the new model, deletes become a bit more complicated: when you delete a Part, any linked SupplierParts are only deleted if they are not linked to any other ManufacturerPart or Part.

I think the same logic applies to ManufacturerParts since they can be linked to multiple Parts. i.e. when you delete a Part, any linked ManufacturerParts are only deleted if they are not linked to any other Part.

I think the key takeaway is that the cascade delete behavior for SupplierParts and ManufacturerParts is the same for all these cases. Whenever the parent object is requested to be deleted, the children are only deleted if they do not have have links to other parent objects.

@eeintech besides the added complexity around deletes, were there other reasons why you think we should not allow 1:many relationship between Parts:SupplierParts/ManufacturerParts?

[eeintech]

I'm going round and round about this implementation, it's more difficult than it looks (at least for me :smile:)

Here's where I stand, bear with me.

There are two possible structures I'm considering to establish the new relationships:

1. Direct relationships: a. between Part and ManufacturerPart instance(s) b. betweenPart and SupplierPart instance(s) c. ManufacturerPart and SupplierPart instance(s)
2. Introduce a "Proxy" model (GenericForeignKey), this proxy model can be either: ManufacturerPart or SupplierPart a. the proxy model can have a direct relationship to the Part instance b. alternatively, the proxy model can have a direct relationship to the ManufacturerPart model (eg. SupplierPart type only)

Assumption 1: set the relationship "type" to be unique

I've laid down the two structures in term of database table.

In structure 1, it requires 2 tables (ManufacturerPart and SupplierPart table), not counting the Part table. Adding, fetching, deleting is simple and directly related to table entry, so database transactions are low. The only "difficulty" is to make sure SupplierPart refers only to either Part or ManufacturerPart, but not both at the same time.

In structure 2, it requires 3 tables (ManufacturerPart, SupplierPart and Proxy table). Adding, fetching, deleting is a little more complex as the relationship are stored in the Proxy table. Similar to structure 1, we have to ensure that Proxy refers only to either Part or another Proxy linked to a ManufacturerPart (and not a SupplierPart), but not both at the same time.

Assumption 2: set the relationship "type" to many-to-many

Proposed by @cdwilson. I'm gonna be honest, this is adding an extra layer of complexity, on top of an already somewhat complex system. Let's dive into it.

In structure 1, the impact is that there will be 2 extra "transparent" database tables created to store Part-ManufacturerPart and Part-SupplierPart relationships (because now any part can be related to any SupplierPart or ManufacturerPart). Adding, fetching and deleting will now involve 2 database tables instead of one (compared to assumption 1) and becomes a somewhat huge headache.

In structure 2, it "only" adds an extra "transparent" database table to store relationships between Part and Proxy, as now there is a many-to-many relationship between those two models. All the previous constraints are quite similar to assumption 1 in term of adding, fetching and deleting, with some extra layer of complexity to keep the extra table up-to-date. But I think the deleting part can be streamlined in a much better manner than if using structure 1.

Take away

It is definitely possible to implement assumption 2. But it requires a lot more effort on the back-end and could potentially lead to unwanted relationships and deletes.

In case we decide upon assumption 1, I believe structure 1 is the easiest to implement. But it is not future proof (eg. lot more work to move database to assumption 2).

In case we decide upon assumption 2, in this case structure 2 looks to me to be the best fit.

I have not made my mind up if I prefer assumption 1 or 2...

[eeintech]

@cdwilson @rco3 I thought about this over the last couple of days and here what I'm settling on.

Compatibility

First of all, I want the new system to not disrupt the workflow of all InvenTree users (many of which are probably happy with the current implementation) just for the sake of a few of us. I hate software that forces you to change something that simply works fine the way it was, just because the new implementation is labelled as "better".

Therefore:

• this new system needs to be enabled through the InvenTree global settings
• the current structure of SupplierPart will mostly stay the same (SKU+MPN and other existing info) except for one new field (see below).

Final structure

I decided upon a hybrid version of structure 1 and 2 presented above:

• ManufacturerPart will directly refer to a Part instance, because I don't see why using a proxy model for a relationship which is not expected to change
• Under the hood, SupplierPart will refer to a new SourceItem model/instance which will refer to either a Part or a ManufacturerPart

If the new system is disabled, SupplierPart will exclusively be linked to a Part instance, through the newly, automatically created SourceItem.

If user decides to hope on the new system, SupplierPart "part" field will let you select a ManufacturerPart too. SupplierPart manufacturer related fields will be hidden in this case, but still stored in the database (in case user wants to revert to the "old" system).

I decided against the many-to-many relationship for the time being, I don't really see the use-case to be honest, and it's a lot more work which I don't think I will have time for in the near future. However, this structure will still allow for this implementation, may it become necessary.

[cdwilson]

First of all, I want the new system to not disrupt the workflow of all InvenTree users (many of which are probably happy with the current implementation) just for the sake of a few of us.

Agreed, I think this is an important goal (especially while we're still experimenting with this).

SupplierPart manufacturer related fields will be hidden in this case, but still stored in the database (in case user wants to revert to the "old" system).

Can you explain a bit more about how this will work? For example, when somebody opts-in to the new system and creates a ManufacturerPart, are changes to the Manufacturer name & P/N continuously duplicated into the hidden fields on the SupplierPart so that the user can opt-out at a later point? Or are these hidden fields just blank in the new system?

What happens to the ManufacturerParts when the user opts-out? If a SupplierPart has been added under a ManufacturerPart in the new system, and the user opts-out, what happens to that SupplierPart? Does it automatically get re-associated to the top-level Part that the ManufacturerPart previously belonged to?

I decided against the many-to-many relationship for the time being, I don't really see the use-case to be honest, and it's a lot more work which I don't think I will have time for in the near future.

I think this is the right call. I think there are definitely use cases to support the many-to-many model, but I'd rather get to something that addresses the 80% use-case quickly with something that's simpler to implement and maintain.

Sorry for all the questions (I will probably ask more!) I think I understand what you're proposing, but I want to make sure. If it's easy to post a diagram showing the final relationships that would be supported, that would be helpful as well.

[SchrodingersGat]

@eeintech I have not had much time to weigh in on this, but here are my thoughts:

I think that the proposed addition of a new SourceItem model is going to introduce more complexity than I would like, and require a pretty significant overhaul of a lot of linked systems (e.g. API / Python libs, etc).

I believe I can distill what you want into a small set of changes, as outlined below. If you believe that this does not cover your requirements please let me know.

Add ManufacturerPart Model

Currently the SupplierPart model has following fields:

• MPN - this is a simple text field that has no relational data.
• Manufacturer - a link to a manufacturer (Company) in the database

We add a new table ManufacturerPart which has the following fields:

• Part - ForeignKey for part_part
• Manufacturer - ForeignKey for company_company
• MPN - CharField

Add manufacturer_part field

The SupplierPart gets a new field, manufacturer_part. This is a nullable ForeignKey to ManufacturerPart

Data Migration

1. For each unique combination of (MPN, manufacturer) in the existing SupplierPart table, create a new ManufacturerPart instance.
2. Link existing SupplierPart entries to the correct ManufacturerPart
3. Delete the existing MPN column from the SupplierPart table

Data Validation

A SupplierPart can still point directly to a Part, and / or also to a ManufacturerPart.

The only condition is that if points to both, then the base Part reference has to match!

---

I don't think it needs to be any more complicated than this, certainly no requirement for extra proxy generic models. And this should seamlessly migrate the data without the end user really seeing any changes at all.

Let me know what you all think.

[cdwilson]

@SchrodingersGat Can you confirm this is the relationship that you are proposing? I tried to include all the scenarios we've discussed so far, and I indicated ones that are not supported by using the red icon (e.g. "One ManufacturerPart to many Parts" and "One SupplierPart to many Parts")

If I understand your proposal correctly, I think this accomplishes the same goals as what @eeintech proposed in a simpler way. If that's the case, I'm on board with the changes you proposed.

[SchrodingersGat]

Correct, that diagram aligns with what I am suggesting.

[eeintech]

@SchrodingersGat @cdwilson

Working on this proposed implementation, will keep you posted on the status.

[eeintech]

@cdwilson Want to be a beta tester? :smile:

If you do:

1. BACKUP your database or create a copy for testing
2. checkout the dev branch from #1417
3. run the inv update (let me know how the migration goes)
4. Enable manufacturer parts under "Settings > Parts"

5. play around with manufacturer parts!

New tab and manufacturer part view under "Part" detail page

New "Manufacturer" detail page with supplier parts table view (access it clicking on MPN link)

New manufacturer tab and view under Company detail page

New manufacturer tab under Supplier Part detail page

New search result tab and manufacturer parts view

I think that's it for now, going to write some test units!

[cdwilson]

@eeintech Yessir! Just left some initial comments in #1417