Or possibly even better add a custom field called house part number that can reference other fields like the value field. (the later feature would need a special bom script)
1 Like
We need a database connector here, a simple single user Sqlite database would meet many users. Why do people try to use spreadsheets as databases?
Because databases need custom software to make them usable for end users. Not everyone has in-house dev team to take care of that. Spreadsheets provide flexibility of setting up your tables exactly the way you want them and pro users can do amazing things in spreadsheets without writing single line of actual code (formulas don’t count even though they are pretty much code statements).
1 Like
For me, it is because I am already heavily invested in Excel and I know how much effort/time it would take to learn SQL to get to the same level. I can work with the data in the spreadsheet even if it is not as optimal as it would be in a database.
From what I know, there are benefits to KiCad being able to interface with a database. It seems that this is the last step to getting KiCad into the “professional” ECAD world.
1 Like
The Libreoffice suite includes Base, this by default uses the Firebird database. So there is no purchase cost to using a real database.
People abuse Excel because they are familiar with it
2 Likes
Yep, that is exactly what I was trying to convey.
1 Like
The direction of KiCad to database should already be doable with the current bom scripting interface. After all a python tool could simply connect to a database instead of creating a csv file. Would just require someone to write it.
The other way round would right now require a huge library generated from the database.
A way to reduce the number of symbols needed is the system i suggest above. (Use symbol fields as a way to finalize the house part number.) Ideally there would still be a way to query the database for available values. I am just not sure that this must be possible from within kicad. One can easily imagine having the database search system open in a separate window and checking which part to use that way. From that then either enter the info manually or use copy paste via the symbol field editor in kicad.
I could even imagine that the designer would define minimum viable part requirements (what voltage to i need. what tolerance, …) in the schematic and the export stage would then query the database for parts that fulfill them. The designer would select the part they want at this stage from the list of already known parts or would be able to add a new one to the database if there is no fitting part in it. (Assuming database represents your in house stock not everything that is available)
This would again fall under the bom export scripts responsibility and would already be possible. If that script then outputs a copy able table view at the end then it would be possible to use the already existing copy paste feature of the symbol field editor to get the final part selections back to the schematic. (Could then later be done with a separate script when eeschema offers a python api)
I can answer only about why I use spreadsheets.
I use spreadsheets since 30+ years for many my needs. The oldest I remember are onces for:
- calculate the saturating current in inductances depending among many others on slot dimension,
- make invoices (with amount word expressed perfectly what in Polish is not easy).
Spredsheet is my each day used tool (to write measurements and do some calculations, to write equations for DCDC converter design).
Why to make my BOM I should learn how to use database (I have never used) if spreadsheet gives me everything I need (may be I don’t know what database could give me more).
Come on. Excel sheet is the best to manipulation vs Database. SQL commands, coding before change some thing ins the database is not a choice for a lot of people I know. SQL is the wrong tool for many cases. It invented only to manage a hug amount of data. Not the BOM!
Digikey have 7,945,428 parts listed, although many of those are duplicates, but that is just one supplier. Obviously one PCB only uses a small set, but the list of possible parts to choose from IS huge.
It’s a pet peeve of mine also, people struggling with huge spreadsheets which are simply a list of items, which should be on a database. Database tools are almost as easy to use as spreadsheets, you don’t need to know any SQL to use them. The reluctance to try new tools does seem to be down to laziness.
I think the main distinction here is as follows. Do you only output a BOM or should the same tool also take care of stock handling and ordering? If you only need the former and just want a way to have it nicely arranged for printing then yes spreadsheets are completely adequate.
For the later a single spreadsheet will become unwieldy fast. Combining multiple spreadsheets can be a hacky solution here but that will only get you so far. Enter a database system. All office tools provide a basic program for using a database system with an interface similar to a spreadsheet tool. This will in most cases be enough to get started, no need to really write sql code anywhere else than in the BOM export script. (And i would assume there are ways to import from csv so you might not even need sql at this stage)
The only place where you will need SQL is if you want a customized user interface. Or if you want to interface to your ordering or billing system should it be a separate tool as well. (That tool might need to be certified by your government such that it is seen as tamper proof. It will therefore most likely produced by this well known company that has this marked cornered.)
This why digikey need database to avoid using 500 excel sheet table. And The user of digikey still love to import there excel, or csv table for ordering.
About 10 years ago we began to use (external to PCB software) spreadsheet to generate BOMs.
During that time our spreadsheet has 570 elements.
We are a small firm. To keep our store small I am trying to use new part (even a nev R value) only if really needed. Not because o cost of that resistors but because of how many drawers you need.
Currently I am moving to KiCad and I have copied that spreadsheet and divided elements to active and dead. Not finished yet but it looks that I will have about 200 active elements starting with KiCad. I think in next 10 years it will rise to something about 500.
Look at this in more simple way: Excel sheet, csv had been live since PC ~invented. And look at database. People already had problem because of the way database work (relationship, and table dead stone after it designed, large cost for add more column in the table, cost software designer design software deeply dependent on how the DB structure…)… Enough to invent another thing call No-SQL (non relational database) .What else, all seem to back to a text file like JSON. What! else!
So I see, database can just be a bunch of text file as a ~row in database, and folder as a table name, and folder after folder as category! — You know, file system are very effective to do this. And very effective to use for distribution, cloud. And data manipulation can be way easy as text editor that have ~same age as the PC been invented. Or a fancy software to put them in a nice interface!
Lotus 123 in the early days before Excel appeared.
I started off with a paper based house number system, but it was still logically a database with house number as primary key. Just assigning a unique house number to every E24 resistor value in a few physical sizes at 1% leads to thousands of IDs
right (I just make it quick, but yes lotus 123 is older…).
Anyway, if the try to store every possible combination without actual using them, or actual part. I can see we are chasing our own tail… N fields, with M possibles values then we got N^M. So it no thing can hold on to that much of data anyway. And then, how would one can event consume or make sense of any thing in that hug big data except for some actually needed. I feel we some how use tech to over complicate our own life/work.
If you extend the notion of “database” to a set of files, then KiCad does have a database, it’s just poorly organised and difficult to interoperate with external tools.
If you’ve only got 500 parts, then the problem is trivial, but I have seen even hobby users request “it’s 2019, I want to query for parts over the web from all known distributors” which is a lot less trivial. That is basically Octopart, but they got bought out.
Anyway, what KiCad needs is an “external part API”, which would allow people to use KiCad’s existing “database”, and any external data source from a simple spreadsheet to a local SQL database to a web based database.
I think the External Part API would be relatively straightforward, there are basically three functions
- query the data set based on some attributes and return a list of matching part names
- provide the complete details for a specific part name
- store updated details for a specific part name
There are some devs interested in new ideas and also respected enough to get their ideas a fair hearing, so we can only hope one of them gets interested. I don’t see anything in the road map though. If anything, KiCad devs don’t want to do anything that leads towards logistics management.
2 Likes
My strategy was to add new value to list each time I decide to use that new value. This gives me two:
- my list is as short as possible,
- I see what values we just have in wall of boxes and can consider if not to use one of them.
1 Like
Hobbyist’s and production environment are using different approach.
Hobbyist will need quick lookup/inventory access to “easy sources” (catalog-based suppliers like Digikey/Mouser/you name it) while for production it’s necessary to keep it tight with own inventory (the less inventory items, the better).
As I understand this discussion, by “parts database” I think of the database of own inventory. KiCad’s (probably as with any other EDA) built-in parts managament is not designed (and should not be) to handle many information related to own inventory management (like stocks, suppliers, leadtimes, prices and even asset location). This must be stored somewhere else.
With small organizations “Excel” approach will be probably good enough. With bigger organizations, probably full house database, with web-based interfaces would yield much better productivity.
Neverhteless, to get KiCad to work with an external database we just need an easy way of keeping parts properties in sync (so kind of “fully defined parts”). There are some quirks that need polishing (like now the Value field is used as Kicad’s part identifier which is sub-optimal imho due to several reasons), but that’s the general direction for things to go.
1 Like
No, I believe this discussion is not about managing a personal inventory, but managing the parts available to a KiCad designer, where or how the parts are stocked is a separate issue.
Most people seem to agree with that.
I don’t think that is possible, since as you say, large organizations already have a part database and KiCad can’t possible know about that. These would require some coding to adapt to KiCads requirements.
Synchronizing an external database can be done now on a batch basis, but it would require some coding to produce KiCad text files specific to the database used. In fact, my company does something similar, each night a batch job runs which synchronises the parts in the company manufacturing database with the part database used for CAD.
What I envisage is a “database plugin” concept, similar to how KiCad handles other plugins. KiCad’s own libraries could be hard coded. A simple default plugin to handle CSV files could be bundled with KiCad, in the same way there are default BOM plugins. More advanced users or third parties could develop plugins for spreadsheets, databases or web APIs.
I tried generating KiCad libraries from Digikey parts, but hit scalability issues. For example, extracting all THT resistors from Digikey leads to a library with 150,000 parts, which causes KiCad to hang forever. The practical limit seems to be < 5000.
Digikey have 230,000 parts in the ICs category, and that is just stocked parts. Obviously, no one ever uses all those parts, but if you are browsing for a part you still need to look through that amount of data. It will never be practical to deal with that amount of data in a text file.
2 Likes