What's the best way to organize components database?
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&amp;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.