For this project (and others as well) I really wanted a nice system for tracking parts/items.  I had been using a spreadsheet for a while but I wanted more out of it, I wanted to be able to create order reports, bill of materials, how much a project would cost, etc.  The spreadsheet system I was using was a bit cumbersome so I decided to make something better.

First I should explain how I categorize parts.  Every item in my database has 2 special classifications, a part type and a part category.  The part types are fairly obvious, these are "Product", "Raw Material", "Documentation","Software", etc.  The part categories are a bit more involved.  Currently I have 111 part categories, these include such things as "Connectors","Spacers","Wheels","IC's", etc.  Each part category has an assigned number range.  For example, the "Connectors" type has an assigned Part Number range from 784000 thru 787999.  This Part Number range is used to automatically generate a new PN based on the part type.  This is useful as it helps to track things.

The Part Number range I currently have ranges from 0 thru 1 million, which is a lot, especially since I only have about 500 items in the database currently.  But this is a nice scalable system that I can use for a long time, if not forever.  I should say that with this Part Number system it is easy to add in more info to it.  For example, if you have 2 different versions of the same part, you can create a part with the PN 533001A and 533001B and the system will still work fine.  One of the things I want to add in the future is the ability to create BOM's based on the part number.  For example, if you have the PN 653002-0X1 and it's designed as a BOM item, you should be able to generate the BOM that this item would represent, given the -* identifier.  

The database I'm using now is based on MS Access with a lot of VBA code behind it.  This was really helpful to me when I started making it, as it's a pretty straight forward system to make a database from scratch.  In hindsight I would have rather used a web-based database so I can use it on other devices.  Maybe someday.

So I think it's time for some pictures.  When you open the database you get the main switchboard:

It is split out into "Search","Projects",Part Update" and "Inventory", which are all self-explanatory.  For part search, it matches on any text in the PN field, item name or description and pulls up a report with all the items that match.  You can also search on Item Category, Supplier and Type.

When you click on one of the search results it will pull up the Item Viewer.  This has all the detailed info on the Item.  

For Items with the type "Locally Fabricated" you can click on the Fabrication Steps link to see how to fabricate this item.

The database also supports projects.  You can add any item (in any quantity) to an existing project with an associated system and subsystem fields.

You can see a project's complete BOM and edit directly from the BOM editor.

Besides a BOM report and an Order report you can also generate a Fabrication report, which is useful to tell you all the things you need to fabricate for this project and all the fab steps for each item as well.

And finally you can see all the current tasks for a project.  These tasks are things you manually enter, they don't link to any items in the database (yet).

This was definitely a brief overview of the database.  There are a lot more cool things that it can do.  For example, every time you create an item it will create a folder in the Library Path that has the following sub-folders: "ANALYSIS,DATA,DOCUMENTATION,ELECTRICAL,MECHANICAL,MEDIA,SIMULATION,SOFTWARE".  This is very useful when dealing with other design programs, now you have a central database for all the item information such as CAD files, datasheets, etc that are all accessible from the database. 

This database was definitely a somewhat specific design for my needs, to use it for other people you will have to change a few things such as the Library Path.  But you can look at it and use it if you like.  Here's a link to the database: database and a never quite up to date help file: help.

I think next time I will talk about my system architecture.