stock control and bom software

[voltsandjolts]

Well my thought was that based on the wisdom of all that have already done this, a structure for a mysql database/schema could be created along with a script to create the database.

If you want to dive into sql here is an example script that creates a (small) new database with a few tables. It's just a headstart to show you the syntax (although I am no expert myself). Run it, for example, in phpMyAdmin.

[mrpackethead]

Here is an example of a record in my "library",  which is a nosql dynamodb.

{
  "part_name": "RES_10K_0603",
  "part_uid": "76ed17de2df8",
  "part_class": "Resistor",
  "library": {
              "schematic":"path/rcl.schlib",
              "pcb":"path/rcl.pcblib"
              },
  "instance": [
      {
      "manufacturer":"ACME_RESISTOR",
      "man_sku": "12345ABCDE",
      "datasheet": "acme.com/resistor.pdf",
      "vendors": [
                    {
                      "vendor_name":"resistorsRus",
                      "vendor_sku": "Rus12345ABCDE",
                    },
                    {
                      "vendor_name":"Ohms4u",
                      "vendor_sku": "Ohms12345ABCDE",
                    },
                  ],
       
      },
      {
      "manufacturer":"BOOM_RESISTOR",
      "man_sku": "XYZ10K",
      "datasheet": "boom.com/resistor.pdf",
      "vendors": [
                    {
                      "vendor_name":"resistorsRus",
                      "vendor_sku": "Boom2130",
                    },
                    {
                      "vendor_name":"DavesShop",
                      "vendor_sku": "10000",
                    },
                  ],
       
      },
            ],
      "fabrication": {
                     "default_placement_type":"machine",
                     "packaging":"8inchreel",
                     "pnp":
                            {
                              "rotate_offset":90,
                              "feeder_size": "8mm"
                            }
                   }
       

[mrpackethead]

NoSQL databases are a bit different, but hopefully, you can see what is going on.

The records Sort key will be part_name.  THis needs to be unique.    IThe partition key is something that will get added to each record automatically..  Once your database starts growing this provides a way for the database to be partitioned easily..  ( https://aws.amazon.com/blogs/database/choosing-the-right-dynamodb-partition-key/ )

If you think this looks like JSON, then you are absolutely correct.  That is how the record is stored in dynamo.

Each part will be given a class,    so you have a way to group various things..   ( resistors, caps, widgets, nuts, power_regs, FETS etc )

THe library feild, contains another object. which contains infomation about what library the part belongs in..  In my case,  all resistors are in a library  called 'rcl'.  If theres additional attributes about the library you want to add, you simple, can add more values.  Records dont' have to have every possible feild.     

Instances of that part, are so that you can accomodate multiple variants of that part from multiple manufacturers, and inside of those variants you can keep track of which vendors sell it.    It might be good to have a link to the part on a vendors web site

I want to keep track of information i'll use for fabrication. with that part.     Can you work out why what i've designed in here, might be problematic?      ( I've provided a better solution in the text file )

[olkipukki]

One of the dramas is that everyone wants to do something just a little different!   I'd like to migrate my database from MySQL to dynamoDB ( no sql ) theres a lot of good reasons for doign that.

Wondering why you would like to do this? 
You can dump your json data to json data field (hope that MySQL has similar to Postgresql) and index some key fields such as mnf_no, part_no etc. into the separate dedicated columns, no?

[olkipukki]

Here is an example of a record in my "library",  which is a nosql dynamodb.

{
  "part_name": "RES_10K_0603",
  "part_uid": "76ed17de2df8",
 

Are you using 1% resistors only? Lucky you!

I always find a bit tricky how to store, process and allocate a generic passive such as capacitors, resistors...

[olkipukki]

  "library": {
              "schematic":"path/rcl.schlib",
              "pcb":"path/rcl.pcblib"
              },

Only one footprint per part, nothing like for high-density boards or 'wider-and-bigger' for easy manual adjustments? Lucky you again! 

[mrpackethead]

Wondering why you would like to do this? 
You can dump your json data to json data field (hope that MySQL has similar to Postgresql) and index some key fields such as mnf_no, part_no etc. into the separate dedicated columns, no?

If you are using modern agile methodologies, a relational database will slow you down. A NoSQL database doesn’t require the level of preparation typically needed for relational databases.

All that being said, there are times that a relational database is a better choice, if you need ACID compliancy, then a relational database is the way to go.

Indicators for projects where SQL is ideal:
(1) Its very easy to store large volumes of data without structure.
(2) a nosql database is very easy to scale compared to a relational database, which makes it more suitable for cloud, deployments. ( its considerably cheaper ).
(3)  using mod

Logic-related discrete data requirements which can be identified up front
Data integrity is essential
Standards-based proven technology with good developer experience and support.

Indicators for projects where NoSQL is ideal:

Unrelated, indeterminate, or evolving data requirements
Simpler or looser project objectives, able to start coding immediately
Speed and scalability is imperative
It is, however, obvious that this is no longer an issue of SQL vs. NoSQL. Instead, it’s SQL and NoSQL, with both having their own clear places, and increasingly being integrated into each other. Microsoft, Oracle, and Teradata, for example, are now all selling some form of Hadoop integration to connect SQL-based analysis to the world of unstructured big data.

[mrpackethead]

Here is an example of a record in my "library",  which is a nosql dynamodb.

{
  "part_name": "RES_10K_0603",
  "part_uid": "76ed17de2df8",
 

Are you using 1% resistors only? Lucky you!
I always find a bit tricky how to store, process and allocate a generic passive such as capacitors, resistors...

In my current parts_library, resistors are assumed to be 1%, unless you state it otherwise.   Bit of history there, and not ideal.



Probably need to add a 'description' object to the document.. ( a document is vaugely equivalent to a row in a sql database ).  Oh, very easy in the non structured world of no sql databases.

{
"part_name": "RES_10K_0603",
"part_desc": "10K 1% .1W 0603 Thick Film"
 "part_uid": "76ed17de2df8"
}

{
"part_name": "RES_1K_1206_5%",
"part_desc": "1K 5% .5W 1206 Thick Film"
 "part_uid": "76ed37fe2df8"
}

[mrpackethead]

  "library": {
              "schematic":"path/rcl.schlib",
              "pcb":"path/rcl.pcblib"
              },

Only one footprint per part, nothing like for high-density boards or 'wider-and-bigger' for easy manual adjustments? Lucky you again! 

Good, point and an annoying issue that i have with my current library, but one that can be easily fixed.

{
"library": {
              "schematic":"path/rcl.schlib",
              "pcb":"path/rcl.pcblib"
              },
}

This was simplistic, in that you'd look for  the partname in your library files.  I dont' even do that now, and have a feild for sch and footprint names.  so we can fix this by adding a couple of objects to the document.

{
"library": {
              "schematic":"path/rcl.schlib",                                                // which file contains the schematics
              "pcb":"path/rcl.pcblib",                                                        // which file contains the footprints
              "sch_name": "RES",                                                             //  lets assume we only have one schematic shape to represent it.
              "footprints": [ "0603_S", "0603_M", "0603_L"]                      // a list of footprints that could be options.
}

[ar__systems]

One of the dramas is that everyone wants to do something just a little different!   I'd like to migrate my database from MySQL to dynamoDB ( no sql ) theres a lot of good reasons for doign that.

What you are doing is actually very similar to my flow. I have similar way of mapping concrete resistors to my generic part numbers (RES-49.9-0603-v1). V1 and V2,3 etc encapsulates all different versions of this generic part, be it different MFG PN, tolerance etc.

I would not want to do things slightly different if I had a s/w doing it pretty close to what I want.

For example, I don't want any cloud based stuff, even though I do use one at the moment. Cloud solution inevitably requires exporting BOM from CAD, and that bring along all the problems I already mentioned.

[ar__systems]

I did not get to Database stage yet, and frankly was not looking at anything other than MySQL, the only thing that I know Dynamo seems quite affordable.

[trevwhite]

I think https://www.mongodb.com/ might be a free document based database.

This could be an alternative that enables local or remote storage of a database depending on the specific user requirements.

[mrpackethead]

One of the dramas is that everyone wants to do something just a little different!   I'd like to migrate my database from MySQL to dynamoDB ( no sql ) theres a lot of good reasons for doign that.

What you are doing is actually very similar to my flow. I have similar way of mapping concrete resistors to my generic part numbers (RES-49.9-0603-v1). V1 and V2,3 etc encapsulates all different versions of this generic part, be it different MFG PN, tolerance etc.

I would not want to do things slightly different if I had a s/w doing it pretty close to what I want.

'if it was pretty close'. That is the real problem isn't it.    They never are 'pretty close' :-)

[quote[
For example, I don't want any cloud based stuff, even though I do use one at the moment. Cloud solution inevitably requires exporting BOM from CAD, and that bring along all the problems I already mentioned.
[/quote]

Cloud is critical for me.     I dont' work at a fixed location,  I have several people working using the same data from different places.   You cant' have multiple copies of the database locally and expect it to keep it in sync.   Its enough of a nightmare right now, keeping the .sch and pcb files synced.. ( i use git ).

I export my BOM, and PNP files from altium right now, and those are inputs for my manufacturing process.  Its all done with local python right now, but my intention is to go one step further and make that cloud based.

[mrpackethead]

I think https://www.mongodb.com/ might be a free document based database.

This could be an alternative that enables local or remote storage of a database depending on the specific user requirements.

THeres a few. mongo probably the most well know.

https://www.infoworld.com/article/3260184/how-to-choose-the-right-nosql-database.html

[mrpackethead]

Is anyone here a javascript/angular wiz?

I can quickly wizz up some HTTP API's to interface to a database, so we can have a play.  My JS/Angular skills are rated at 0.1/10 and i dont' have enough hours in the day to make it work.

[IanJ]

One of the problems you'll find is that even the non-cloudy stuff doesn't want to sell you an outright licence for something they consider business critical. Even when they take an upfront fee they then want an annual maintenance contract

We've been developing and selling MiniMRP for over 20 years. In that time we've NEVER need to bill anyone for monthly or annual maintenance. Our software really is a once only payment - buy once use forever. We have enough new users coming on board that we've never needed to 'screw' our existing user base for more money.

We still have users using their original version that they purchase 10 or more years ago and it still works fine in Windows 10.

Having said that we do, very rarely (maybe every 5 years or so) release a whole new version with more features and if somebody wanted to switch to that version they'd need to pay (usually around $50) but nobody is forced to upgrade and, as I said, older versions still work just fine with latest versions of Windows.

Oh! I'd also like to add that MiniMRP version 5 was recently updated to work with WINE under MacOSX and Linux. So, run the Windows version of MiniMRP in Linux without a VM. This WINE update is quite new so it would be great if any Mac or Linux users would test that and give us some feedback

http://www.minimrp.com

 

I thought I'd try MiniMRP but got confused out of the box........appreciate some help with terminology.

So I have a product that I already have an excel spreadsheet for the Pcb BOM and the other parts associated with assembling the product (enclosure, DC charge cable, CE stickers etc)........so where do I start?
I don't have a master inventory for all my parts on the shelves......I more or less wing it but tell you the truth I wouldn't mind have a database for this (except most SMD resistors, jelly bean parts etc).

What is an ASSEMBLY
What is a SUB-ASSEMBLY
What is a CLOSED SUB-ASSEMBLY
What is a MISC ITEM

I can export one of my Pcb BOMs to a CSV, headings and example line as follows:

Code: [Select]

PART  VALUE             PACKAGE  DESCRIPTION                SUPPLIER  PART_NUMBER        COST_1off  COST@break  CURRENT_STOCK
C11   10uF 50v Ceramic  C1206    Samsung CL31A106MBHNNNE    Digikey   1276-6736-1-ND     0.240      0.065       Not known

Appreciate if you could reply in terms of Pcb's, products that may consist of Pcb's c/w enclosures etc etc....

PS. I like the GUI.

Ian.

[ar__systems]

Cloud is critical for me.     I dont' work at a fixed location,  I have several people working using the same data from different places.   You cant' have multiple copies of the database locally and expect it to keep it in sync.   Its enough of a nightmare right now, keeping the .sch and pcb files synced.. ( i use git ).

I export my BOM, and PNP files from altium right now, and those are inputs for my manufacturing process.  Its all done with local python right now, but my intention is to go one step further and make that cloud based.

Not exactly what I meant. I mean there a several online BOMs. All of them use some kind of table input. And THAT is what I don't want to have, because it becomes a sync problem. What I want to have is that my .brd file is synced to version control, and purchasing people could open the BRD file in the BOM application (online or offline), and send purchase order to Digikey or whatever directly.

So I'm not exactly against online apps, I just don't want ones that create a separate document (BOM) not synced to the board.

Clearly, that is not much of an issue for finished boards, but I'm more concerned with boards in active development, where I might go through several revisions, and if each one creates a new BOM document, it just multiplies the potential for errors.

[Ribster]

But don't you freeze the BOM version once you release it ?
While you are working on the version, there is no release. Once you deem it okay for a trial run you generate a version of the BOM.
In essence the released BOM is always synced with the board, since you generate it from there...

[ar__systems]

Yes of course, when the board reaches maturity the BOM is fixed. But then again, I don't work on that board anymore. Historically I know that I had tons of problems with errors in BOMs, and I can't really describe all of them. Sometimes an incorrect package of transistor is specified, sometimes some resistors are changed at the last moment and the change is not reflected in the board file. The problem is that some of these changes happen over a period of time. It can be a few month between the customer takes prototype boards from me, test them, find an issue that requires component change. At that moment I need to update the BRD file and BOM file. If I'm not careful, I might end up updating only one of them. Few more month down the road they go into production and request a final BOM. At which point I have no way of verifying if the BOM matches the BRD file. It has happened in the past that I would send previously generated BOM forgetting about later changes.

It sounds a bit stupid as I explain it, and surely some of this can and should be avoided with more disciplined approach. But that is the reality and so I'm moving towards the process where I give customer ONLY board file, and let them generate BOM from it.

[ddavidebor]

Hi
Anyone using some good, clean software that manages stock levels of components,etc and BOM creation. I dont want to pay monthly for a cloud based system. I just want something that I can record all the stock I have and relate that stock to BOMs. This is also good to then use at the design stage when I need a part as I can find what I have on the system that might already be suitable and in stock for prototypes as well as production.

This kind of software, I am sure has been out for decades but all the current stuff I find is cloud based and monthly charges.

What are people using?

Trev

Hello,
I have been having issues of this sort since forever. Still not solved!
I manufacture some equipment that has expensive components (super low noise low current low everything instrumentation amplifier) for example and need to manage this sort of stocks.

For the complete products, I am using ERPNEXT, which is quite incredible. I also know of https://partkeepr.org/ that has been abandoned but just a year ago or so and is quite good.
I have rolled my own solution for Altium as well, made from a database and an interface. I am also planning to sell it BTW.

Based on my experience working for a company with horrible bom management, the trick to have perfect bom is NEVER touch any component inside the design, but instead follow a workflow like the following:

1. choose your components carefully. You don't need 12 different opamps in 20 products.
2. create a fully documented component in the library included symbol, footprint, 3d model, datasheet, description, simulation models if needed
3. never use a component outside that library
4. repeat
5. profit!


I have also imported some standard components in advance, such as Yageo commercial SMD chip resistors, that are manually integrated with alternatives as needed.

BOM should be exported automatically from your ECAD and never touched after that. a Strong workflow is the only way to not make mistakes.