# What's the best way to organize components database?

Thinking of building a local components database which maps company part number to multiple manufacturers. Assume SQL type thinking.

Option 1. One table with company part numbers as primary key, fields with component values, names of symbols, and footprints. Another table with company part numbers as primary key, and fields with manufacturer and manufacturer part numbers. A join on company part number is performed to get multiple manufacturer part numbers per company part number. Advantage is simplicity. Disadvantage is I don't get a list of components and their values for each manufacturer which would be nice, and I need to explicitly maintain the second table which does the mapping, which could have mistakes.

Option 2. First table same as above. A different table for each manufacturer with all their components and values. Join on values. Advantage is mapping between company part number and manufacturer part number is automatic based on values and I don't have to curate the mapping. Also it would be nice to have a separate list of components from each manufacturer. Disadvantage is sometimes you want to exclude components based on rohs or something else, so that's a new field in both tables. Instead of curating an explicit mapping, which could be arbitrary (for better or worse), I now need to curate fields across multiple tables and make sure each manufacturer table has the same schema.

Which is best?

• For what it's worth (nothing), unless your business is going to be critically reliant on this bespoke database, I would council you to buy one rather than write one. IT projects are notorious for failure and what you think is just a simple knock up of 3 tables actually isn't. The existence of such a simplistic question (no offence) indicates you don't realise the huge development time you're committing to. Databases are very complicated. And the documentation? And the GUI? Just buy one off the shelf. This is actually what's best. – Paul Uszak May 21 '17 at 23:11
• Meh, the concept is basically "where to start". I've spent too many years dealing with Altium, and have had some experience with Orcad CIS. Both of those are barely adequate. I have many ideas for how to improve things, but I only have maybe a few hours in the evening to dink around, so in reality this won't go beyond a toy project. Recently I've been dealing with Arena PLM tool, not really set up for the engineering per se. It's clear Arena seems to automatically manage all the tables in the background, but for 95% of BOM management, a naive approach is probably the best starting point. – Sonicsmooth May 23 '17 at 3:34
• Ah no, sorry. I didn't mean to imply using an EDA tool. I meant looking around for general component / parts management apps. – Paul Uszak May 23 '17 at 14:07
• Ya after initially scoffing at Arena usage at my current workplace, I now realize the importance of a decent PLM tool for tracking all sorts of stuff, version control, interfacing with purchasing, contract manufacturers, etc. This is a step up from my previous work whose database did not manage revisions, I don't think. – Sonicsmooth May 24 '17 at 16:41

There may be a lot of web sites with info on MRP II RDBMS on SQL for EMS. Only you can decide how to create each database for efficiency so that you don't have just 2 or 3 tables. Each edit to a field is updated live and you want to consider Rev control of each data base for sanity and effectiveness. Each user will have different needs. Purchasing, Board design, BOM design, Manufacturing , Quality and Accounting. Each set of fields should be indexed or keyed according to use and relation to each other for optimal size , speed and avoid redundancy or putting everything into compartmentalized or Departmentalized tables.

Knowing how much you can support and design of each database parameter, type and size from the start is critical. Start with a spec of all user needs, reports, parameters, frequency of use and then design the tables. Then rank Must Haves and Nice to Haves.

Here is an example of one page of one report of my Master Registry which did not include PCB design parameters. It was for Commodity Code 15 on connectors, showing description, Stock on Hand (SOH) and Bin Location. This Report used only 3 tables. Some fields could be unique and others blank.

## personal anecdotes

I also did the above from '94 to '96 in Foxbase, when I was Ops Mgr for an R&D firm that could make rapid prototypes. The relationships of RDB table was done graphically with independent index and key fields with parameter attributes for each field.

The Master Registry was a book of all in-house Engineering Part numbers, which used commodity codes, and clear concise descriptions. It was a lot of work, but necessary.

e.g. Don't use SCR as the 1st descriptive word if you also have SCR screws use hierarchical key words. Choose as many Tables as needed with at least one relational index, usually, but not always the part number.

The Master Registry was used in all BOM's, my stockrooms, Purchasing for RFQ's and PO's so we could send all the unique part numbers to distributors with matching commodity codes, or Mfg by a unique list per source and put the supplier price cost, qty values into another indexed database for doing Costed BOM quotes.

The products were micro/RF modules for automated meter reading and 2-way network telemetry.

There were dozens of reports which included a paper binder of all the in-house part numbers organized by commodity code with abut 10k part numbers and typically 2 to 4 approved vendors for each part.

Standardized reports for; Costed Boms, design selection, Stock bin location, daily BOM pick lists for proto assembly with organized trays with array numbered 35mm film container for each SMD part on cut strips (hand soldered.) etc etc.

I did a lot of work the database structure, but then got a genious Comp Science grad student working for us to help me out on all Foxbase reports in evenings.

At that time Design was done on MAC's with PC's for PO's as it was faster with big spreadsheets for Quote merging. Accounting was separate on Great Plains. It worked although clumbsy at times but much easier and cheaper than any MRP II system I reviewed or had used in the past.

We had 25 Hardware Design Eng. 25 S/W designers and I had 5 in production, 1 in purchasing 1 in Quality and I ordered all the PCB's quick turn and occasionally designed custom Test Jigs.

There is no single "best". It depends on your requirements, what software you use, what the capabilities of your existing software are, etc.

I use Eagle to design boards, and long ago created my own system around Eagle for generating BOMs. Cadsoft later brought out some ULPs and the like for the same purpose but that work differently. Since mine work, and quite nicely at that, I continue to use my existing software.

In Eagle, I use attributes on parts for defining things like manufacturers, their part number, suppliers, their part numbers, values, description string for the BOM, etc. This by itself works fine except for dealing with in-house part numbers for multiple different customers.

To do that, I have a series of CSV files stored in a place known to the BOM software. These files relate the part information in Eagle to in-house part numbers. The entries can also be set up to use the same stocked part for different purposes, and then group the result on a single BOM line.

Here is the header line common to these CSV files:

Desc,Value,Package,Subst,Inhouse Acme,Manuf,Manuf part #,Supplier,Supp part #


Note the "Inhouse Acme" field. This sample file is for the Acme customer, and that field gives the in-house part number for Acme. I keep one CSV file per customer.

Each directory in a tree where I keep Eagle files can have a "housename" file. The lowest down the tree to a particular leaf node of such files provides the house name. This is how my system automatically handles multiple customers, each with their own part numbering scheme.

The BOM software has the data specified for each part in Eagle, and looks for matches in this file. Matches can be by value or definitive. For example, if two parts have the same manufacturer and manufacturer part number, then they are the same part definitively. Otherwise, if the description, value, and package match, then the part matches by value.

Here is a snippet of such a file:

"Capacitor, unpolarized",22pF,SMD-0805,Yes,9133053,,,,
"Capacitor, unpolarized",47pF,SMD-0805,Yes,9133052,,,,
"Capacitor, unpolarized",100pF,SMD-0805,Yes,9133055,,,,
"Capacitor, unpolarized",200pF,SMD-0805,Yes,9133056,,,,
"Capacitor, unpolarized",1nF,SMD-0805,Yes,9133058,,,,
"Capacitor, unpolarized",10nF,SMD-0805,Yes,9133061,,,Mouser,81-GRM40X103K50D
"Capacitor, unpolarized",100nF,SMD-0805,Yes,9133049,,,,


These are a bunch of 0805 capacitors that only differ in capacitance. The capacitance value is added to the base part in the schematic, and has to be entered the same as here for it to match. If a match is found to any of the above, the in-house part number is known. For example, such a cap given a value of "1nF" in the schematic will pick up in-house part number 9133058.

Another useful thing my software does is collapse all uses of the same in-house part. Here is another snippet from the Acme file:

"Capacitor, unpolarized",1uF 10V,SMD-0805,Yes,9133054,,,,
"Capacitor, unpolarized",1uF 25V,SMD-0805,Yes,9133054,,,,
"Capacitor, unpolarized",1uF 35V,SMD-0805,Yes,9133054,,,,
"Capacitor, unpolarized",1uF 50V,SMD-0805,Yes,9133054,,,,
"Capacitor, ceramic",1uF 50V 10% -55+100C,SMD-0805,Yes,9133054,,,,


Note that these are all 1 µF 50 V capacitors in 0805 surface mount package. Acme has decided to only stock 50 V parts, then use those even when lower voltage parts would do. That's a very legitimate stocking strategy, especially for cheap parts like these.

However, I still want to show on the schematic that only a 10 V part is needed. In that case, the 10 V part description matches the first line, and part number 9133054 is assigned. Since the next line has the same in-house part number, it matches that line too, and the description is updated to "1uF 25V". This continues until it gets to the bottom line shown, where the description is updated to "1uF 50V 10% -55+100C". That's what ends up on the BOM for any of the capacitors listed. The schematic might show a "1uF 10V" cap in one place and a "1uF 35V" cap in another, but they both get lumped on the same BOM line as "1uF 50V 10% -55+100C" for this customer only.

If Acme ever decides to stock 1 µF 35 V parts, this section of the file would be updated. When the BOM is re-run, the 10-35 V parts would be on one BOM line and the 50 V parts on another.

When I use a new part they don't stock, it shows up on the BOM with the in-house part number blank. Someone then assigns a part number, feeds that back to me, and I update the file. After that, my BOM system automatically fills in the in-house number for that part for subsequent designs or BOM runs.

This may sound complicated, but it's really been very nice to use.

• What do you mean by customer? Are you building boards then selling them to customers? If that is the case, does each customer get its own part numbering scheme for the components? – Sonicsmooth Mar 20 '17 at 16:25
• @Soni: I mean organizations that I design boards for. Usually the organization want at least the option to manufacture the board themselves, so they want BOMs to show their internal part numbers. – Olin Lathrop Mar 20 '17 at 16:59

None of the above. But Option 1 is less worse than Option 2.

I pick door number 3

Use a company part numbering system that contains the values of the component it identifies.

Not knowing you company inventory that's about all I an offer.

Back in 2006 I developed an inventory system with meaningful part numbers. A single table contained both parts that were used to build the products and also the products and sub-assemblies as well.

If you had a part number just by the part number you would know it was an 0603 1K 1% resistor

A part could not be entered without all the required values for a part of that type.

I'd do it with three tables...

1. Supplier <- lists supplier info

2. Supplier_Parts linked to Supplier_ID, includes field Company_Part_ID

Which parts are available from which suppliers, and supplier catalog #

3. Company_Parts listed by Company_Part_ID

Company_Part_NO and with tech info of parts, anything company specific about that part


The rest you can do with queries.

• Why a separate table for company parts and vendor parts? – Misunderstood Mar 19 '17 at 1:51
• I changed it after a rethink @Misunderstood I was getting confused between supplier and manufacturer. – Trevor_G Mar 19 '17 at 1:51
• I just like being able to get everything associated with a part or assembly in one query using one index on one table. The query search value could be masked to include or exclude. – Misunderstood Mar 19 '17 at 1:59
• @Misunderstood, that adds a lot of duplication though which opens the door for errors. Three tables would be the minimum. in my opinion, assuming you have multiple suppliers for a single part which is not uncommon. – Trevor_G Mar 19 '17 at 2:05
• We are not on the same page. Duplication has nothing to do with what I am referring to. Opening doors to error is a alien concept to me. – Misunderstood Mar 19 '17 at 2:26

You might want to take a look at BOMIST: https://bomist.com It's a parts inventory and BOM manager software for electronics. It runs locally, no server nor setup or cloud needed. And it's integrated with Octopart, meaning you get pretty usefull real-time info about your parts (think getting real time availability and quotations when ordering parts).

DISCLAIMER: BOMIST's developer here.

• Nice. Just curious, are you the lone developer? What language did you write this in? How long did it take? Did you set up the website too? – Sonicsmooth Nov 1 '17 at 16:03