Database Library Support for Multiple MFRs & MPNs

[SpaceSystemsEE]

Hi Folks,

I am trying to set up my organization’s component database with KiCAD v7 and appear to have hit a snag.

Based on my reading of the documentation and the format of the configuration file, it appears there is no way to associate multiple manufacturer’s and manufacturer PNs with a single internal PN.

This has been extremely common practice for every company I have worked for. As an example, you might have a single record for a 10k ohm resistor in a particular package with particular temp coefficients, tolerances, power ratings, mil-specs, etc. But equivalent parts are available from multiple manufactures. These are are associated with the same internal PN and then the BOM would list the different pairs (up to some definable limit) so that the contract manufacturer, component engineer, etc is aware of the different acceptable substitutes.

To achieve this, I have a separate table in my database with a unique key that is NOT the IPN. Each record consists of IPN, MFR, and MPN and a query can be made to pull up every MFR and MPN pair associated with the same IPN.

Right now, it looks like the only way to use separate tables in KiCAD is to have separate libraries. I’d like a way to split the data for one library across multiple tables and keep everything linked in KiCAD.

Is there some way that I have missed that would allow this to work in the current version of KiCAD? If not, how should I go about recommending this as a feature?

Some additional info about my database:

5 Tables:

• Main - Contains IPN, Category, Value, Description, Symbol, Footprint, Lead Finish, 3D Model, Datasheet, etc
• Performance - Contains min/max performance specs used for automating Power Stress Derating Analysis
• Manufacturing - Described above. Associates multiple MFR-MPN pairs with IPNs
• Procurement - Similar to Manufacturing, but for authorized distributors
• Compatibility - Associates similar IPNs with each other. Usually they will have the same form, fit, and function, but different quality grades.

[OookLout]

Ah, the eternal Component Engineering conundrums! I sympathise.
Most companies I know have a separate database for alternative sources and similar ‘metadata’.
This is indexed by Company Component ID (CCID) and is used by Purchasing and Component Engineering who may well add their own live data like preference, usage, and supply-chain problems.
Design Engineers are often naive about real-world component issues, and need to be able to work with clean idealised information. If there is no component engineer to sort out their component IDs then one or more experienced engineers should spend time on this essential work.
This strategically valuable metadata does not belong on the CAD database which in a commercial firm should only have enough info to run the schematic capture, simulation, layout and test functions. This minimises the impact of changing the CAD system (or indeed operating two CADs in parallel as I have seen - open source workstations and high-end licensed seats.
My advice is: Do not underestimate the value of a separate metadata database. It can avoid the quirks of the individual ECAD and MCAD systems, it will match the company’s needs, it will have a long and valuable life and be loved by the buyers (who have a lot of clout and can really influence the bottom line when given good information).

[SpaceSystemsEE]

I mean OrCAD Capture CIS has had this functionality for quite a long time. Not 100% confident, but I suspect Altium does as well.

While I admit I don’t need the Distributor info in the CAD tool, allowing for multiple MFR/MPNs is, in my honest opinion, a must for a serious ECAD tool. This is all information that can be used in a BOM directly generated by the CAD tool.

Having the performance specs is also incredibly useful as you make the schematic and as you perform analyses based on the BOM.

There’s a lot that KiCAD does better than OrCAD, but this is something that deserves some attention.

It also doesn’t strike me as particularly difficult to do. You just have to allow for a much more generic data structure instead of 1 library per table. In fact a customizable query based approach seems like the easiest way to accommodate the diversity of databases in use.

[Claudio.Lorini]

@SpaceSystemsEE

I mean OrCAD Capture CIS has had this functionality for quite a long time. Not 100% confident, but I suspect Altium does as well.

I think that you are misplacing ‘schematic symbol’ and ‘Footprints’ alternatives, which OrCAD CIS offers (there are feature requests open on gitlab to implement that in KC) with IPN equivalence, that is a problem better solved at DB level and not ECAD level.

[craftyjon]

As noted on the issue you opened, you can do this with SQL views. KiCad does not know or care about whether your parts have zero, one, or multiple MPNs. You just need to decide which fields you want to bring into KiCad, and which you leave in your database. If the fields you want are contained in multiple tables, create a view (virtual table) that joins them together and you should be all set.

[teletypeguy]

If you want to substitute a part number that is footprint-compatible and functionally identical, but has a totally different number, you could define db fields for substitute parts, and manually swap them in when you are ordering parts, if needed. I have a Note field where I tuck such info. What if my garden-variety DMG2301L p-mosfet is out of stock? Well, I can drop in a FDN340P, or NX3008PBK, or a dozen other parts. But none of these are a direct second source to define in the db – they all need separate part numbers, but can probably be substituted at purchasing time as needed (and documented for the build).

If you have multiple tables of part categories in your db (C, R, U…) to manage a large db you will still want to mandate that the id keys are unique and never repeated across these primary tables – this is your master parts database with a single id number resolving to a single part.

In each db record you can have a part id, description…, a manufacturer’s number, vendor, vendor part number yada yada. I have a primary and alternate vendor defined in my db, plus jlc numbers for when I have them make a proto board (partidnum and other fields not shown in this snip):

For the primary and alternate vendors parts fields, they need to be essentially identical parts. It might be the same part from mouser and digikey. It might be both from one vendor but one part is a reel and the alt is a tube. It might be an alt that is a higher temperature part. For the jlc fields I don’t care if it is a quite-different part, just close enough for proto work. But for the primary and alternate vendor parts I want them to be drop in identical parts with the main part number the same (perhaps varying in suffix letters for temperature or whatnot). Your db needs may be different than mine.

However, I have also created a table for Alternate-Symbol use, which has partnumid fields that ARE duplicated from the primary part table, and the record only differs in the schematic symbol it uses. This is a handy way for me to use an alternate schematic symbol, and still have a proper bom generated with all the same info.

You may be able to use a Substitute-Part table that uses the same approach to define your substitute parts. This way you can decide at design time which part has best availability, and you bom will have the same partnumid but the substitute mfg/vendor fields.