PLM - Part 2: One too Many Dilemma

If you missed PLM - Part 1 'One too Many Dilemma'.

Instead of jumping right into the 'many to many relationship' we are going to build our way up to it, starting with 'one to one'. This helps to understand how and why the 'many to many relationships' exist in PLM systems.

Relationships:

'One to One'
'One to Many'
'Many to Many'

Most PLM users have no idea what a 'many to many' relationship is or how it effects their company's products. 

PCB designers with CAD library responsibilities need to understand PLM systems and how PLM impacts their CAD database library. 

If you are still reading, I assume you have a CAD database or you thinking about creating CAD database library solution.

Usually people will glaze over when you try explain the 'many to many' relationship.  Until they 'get it' they will fail to understand PLM integration problems with CAD libraries.  

After management spends multiple thousands of dollars on a PLM system they really don't want to hear or believe there are integration issues. They are more inclined to believe they just haven't got the right people involved to solve the integration problem.

Experienced database designers are very familiar with the relationships listed above and therefore they 'get it'.

MCNs are Material Control Numbers (Company Part Numbers in a PLM)

Let's go through some relationship examples:

'One to One'

In the 'One to One' example below one MCN has been created in the PLM system and only one Manufacturer Part Number has been assigned to the MCN.

MCN: 624-0016-00 (Company P/N in PLM)
MCN Part Description: CAP 0.047uF ±10% 10V X7R 0402

Manufacturer: Murata
Manufacturer Part Number:  GRM155R71A473KA01D
Mfr Part Description: CAP 0.047uF ±10% 10V X7R 0402 (1005 Metric)
MCN: 624-0016-00

Note that the MCN Part Description should include all critical specifications for the part.

'One to Many'

PLM systems by design have MCNs with alternate manufacturer part numbers. Obvious reasons include; second source, availability and price.

In the 'One to Many' example below a second Mfr P/N has been added to the same MCN.

Notice that the second mfr P/N added to the MCN has a 16V specification vice the 10V specification listed in the MCN Part Description.

The 16V part is actually a better than part, because it's specifications exceeded the requirements in the MCN Part Descrption.

Since we have one MCN and more than one Mfr P/N approved for the MCN we now have a 'one to many' relationship.

MCN: 624-0016-00 (Company P/N in PLM)
MCN Part Description: CAP 0.047uF ±10% 10V X7R 0402

Manufacturer:  Murata
Manufacturer Part Number: GRM155R71A473KA01D
Mfr Part Description: CAP 0.047uF ±10% 10V X7R 0402 (1005 Metric)
MCN: 624-0016-00

Manufacturer:  Yageo
Manufacturer Part Number: CC0402KRX7R7BB473
Mfr Part Description: CAP 0.047uF ±10% 16V X7R 0402 (1005 Metric)
MCN: 624-0016-00

'Many to Many'

Let's look at the 16V approved alternate part for MCN: 624-0016-00.

Manufacturer:  Yageo
Manufacturer Part Number: CC0402KRX7R7BB473
Mfr Part Description: CAP 0.047uF ±10% 16V X7R 0402 (1005 Metric)
MCN: 624-0016-00

This 16V part is a better than part  for the original 10V part.  As it turns out this part was already in the database and was assigned to another MCN which required 16V.

After the 16V part was added to the MCN for the 10V part the same Mfr part number will exist in the PLM system under two different MCNs. Hence the 'many to many' relationship.

PLM systems by design support 'many to many' relationships. It is through these 'many to many' relationships that PLM maintains products in production.

'The Quandary'

While the many to many relationships found in PLM systems are a perfect solution for sustaining products in production, this relationship has issues for PLM and CAD integration.  

CAD libraries use 'one to one' and 'one to many' relationships to associate schematic symbols, footprints and parametric data to a specific part number.

You have been asked to keep the CAD library up to date with MCNs associated with Mfr Part Numbers. You get a Excel report from the PLM system to back annotate the CAD library with MCNs where Mfr Part Number in CAD = Mfr Part Number in PLM.

As shown previously the Mfr P/N for the 16V part is now in the PLM system with two MCNs associated with the same Mfr P/N.  You now have a 50/50 chance of updating CAD library with the correct MCN, that is where the MCN part description will be an exact form, fit and function equivalent to the Mfr P/N.

Back annotating the CAD library with a parameter like Mfr P/N from a PLM system which has 'many to many' relationships could compromise the data integrity in the CAD library.

If the 10V MCN which is now associated with 16V Mfr P/N in PLM is assigned to the 16V Mfr P/N in CAD library then designs which require the 16V part would be compromised.

Conclusion

Careful attention to details are required when assigning MCNs to Mfr Part Numbers in a CAD Library to avoid compromising form, fit and function of the parts defined in PLM.

The specifications of Mfr Part Numbers assigned to a Company Part Number (MCN) must be be equivalent or better than the specifications called for in the MCN.

A surrogate key provides the flexibility needed to deal with scenarios, like:

1. MCNs with one or more Mfr P/Ns
2. Same Mfr P/Ns used in one or more MCNs

The scenarios described above are frequently found in PLM systems.

To support a product through out it's life cycle requires having alternate Mfr P/Ns which have been qualified and approved for use in the PLM system.

Within a PLM system it is possible to have a Mfr P/N listed under one or more MCNs. 

Using a surrogate key in your CAD Library Database can solve the PLM to CAD Library integration issues created by the many to many relationships that exist in PLM systems.

In Part 3 of this series we take a look at a real world example of the 'many to many' relationship that can exist in a PLM system.

DBLib

An Altium DBLib or SVNDBLib file links your CAD library to a selected data source.

Open the DBLib or SVNDBLib file in Altium.

Select Database Type,  the Parts Backend database is an Access 2007 file.

Then browse and select Parts_Backend.accdb


Select the Table Browser tab at the bottom of the form > Right Click on a record to add or edit parts to the database.

click on image to view





















The data source (Parts Access Database in this example) is mapped using a DBLib file.

Both DBLib and SVNDBLib have some very useful options.

Options include enabling which parameters are 'Visible on Add', this applies to new parts placed in the schematic.

Shown below are my preferred settings.

click to view image

Update Values controls which parameters will be modified when using 'Update Parameters From Database'.

Related Links:

Organize Parts
Lifecycle
That's It !