KiCad Database Design

Tools
1

@cbrake has demonstrated a working database for KiCad library parts using sqlite (see GitHub - git-plm/parts: parts database and KiCad Libraries). It would behoove us regarding the implementation of any database to try to come up with a standard set of field names for the parameters of various component types. Every library component has 4 reserved field names: “Reference”, “Value”, “Footprint”, “Datasheet”. While 3 of these are obvious it’s not clear what is the best way to use the ‘Value’ field. It seems that for all of the KiCad designs I’ve encountered the Value field is usually the same as the symbol name. The symbol name in the ascii format libraries is in effect the database key for these libraries but this is unsuited to an SQL database which requires a unique key for each entry. The Value field has been used for parameters of resistors and capacitors and other components. It seems the Value field is a good candidate for the SQL database key since that field is required and will always be present on every symbol. The symbol name only needs to be unique in its particular library. Two different resistor libraries could exist for example, each could have identical part names, but each part still must have a unique SQL database key.

Additional fields may be assigned to symbols as required. When selecting parts from multiple libraries, if field names are not broadly standardized across all of the various component libraries this can create a massively confusing bill of materials since a column in the BOM will be added for each unique field name encountered in a schematic or board file. Creating new symbols is tricky since KiCad does not have a “template” feature for each type of part being entered into a design. Most of the time a new part is entered simply by copying an existing part with all the fields already defined then simply modifying and saving the copy. I have to keep a “cheat sheet” handy listing all of my field names in use for each part type to prevent polluting my BOM’s. Achieving field naming uniformity is particularly important for designs that are collaborations. Everyone needs to be on the same page as there is no enforcement mechanism for field naming conventions when creating new parts.

Field names that I have been using recently are:
‘Part Number’ - customer’s or our internal source control documentation part number
‘Desc’ - This is a short version of the part description.
‘Description’ - This is the full part description. Both of these are available when browsing DigiKey parts.
‘Height’ - in mm, useful for PCB placement area rules that have height restrictions
‘Part Name’ - This is the name of the symbol in the ascii database. Provided here so that it can be placed in the BOM if necessary since the part name is not otherwise accessible if you wish to find the part to make a change, for example.
‘Val’ - used for resistance, capacitance, inductance
‘TOL’ - tolerance
‘Volt’ - voltage

Because customers always want multiple sources for each component I have definitions for 3 manufacturers, each manufacturer can have 3 alternative part numbers:
‘Mfr A’
‘Mfr A P/N 1’
‘Mfr A P/N 2’
‘Mfr A P/N 3’
‘Mfr B’
‘Mfr B P/N 1’ etc, etc.

Yes, field names can have spaces and special characters, but be careful this doesn’t jack up exporting a BOM to whatever format you wish to use. CSV file with quoted fields is the safest.

2

@jeffsFOM thanks for the review and suggestions!

Yes, naming is the hard part.

I also found another project in my wanderings that looks interesting:

Likely overkill for me until I get beyond the prototyping stage, so will probably continue down the Database route for now …

But looks like a neat project to keep track of.

3

Rather than reinventing the wheel, as KiCad becomes more useful in an enterprise environment and not just for hobbyists or one-off projects, it might be better to explore how to integrate the existing database usage features of KiCad with existing MRP systems. Proper management of the parts supply chain is a huge challenge for any manufacturing enterprise. If migrating from another CAD system all the parts needed may already be in inventory and under MRP control, so the thing to do would be to create the KiCad parts databases from the existing or a subset of the existing parts currently in the enterprise MRP system. It should be fairly easy to export parts into libraries of .CSV files then create databases for KiCad users as @cbrake has demonstrated above.

4

I think the HTTP Libraries feature in KiCad 8.0 will greatly improve the possibility of integrating with other systems:

This allows you to write a shim between the systems that exposes an HTTP endpoint, and then KiCad gets its library information through that interface because the database likely already exists in the other system. This also allows you to translate the data from any DB schema into what KiCad needs – this is lacking with the current Database feature – your DB pretty much needs to be in a format that KiCad expects – parts in one table, etc. A more likely schema is one table for parts, and then another for parameters, so your parameters are not fixed by columns in the main table.