Electronics Inventory Database - Table Structure

The database structure depends to some extent on what you want to do with the data. Is it to calculate the value of the overall collection, or is it to find out in which bin you put how many resistors of a given type, or what?

To answer your question about varieties with many common fields: the classic solution is to create a parent table with the shared fields, and child tables for each type. Those would be 1:1 related to the parent.

Hi Tom and many thanks for your reply.

The database will be eventually aimed at doing the things you listed and more (for example, I want to be able to create "electronics projects" in it that have a Bill Of Materials with specific components. The database should know if all the components are in stock and if so, where they are (long term storage, storage box number and shelf, or even already on the workbench "frequently used" storage bins.

BOM is a well-understood concept in database design, and so is Inventory Database, and the concept of Assembly made up of smaller Assemblies/Parts, so I suggest you do some reading about these concepts. No need to reinvent the wheel.

Certainly an ambitious project if you're new to database design and/or Access programming.

You might like to take a look at BoM.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to amend the form design accordingly.  

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

This little demo file illustrates a means of simulating the recursive querying required to generate a bill of materials to an arbitrary number of levels.  While products like Oracle, SQL Server etc support recursive querying, JET/ACE SQL as used by Access does not, so in this demo it is simulated by progressively inserting rows into a temporary table in an external file.

My demo does not model parts as types/sub-types, so if you use a type/sub-type model of one-to-one relationships as described by Tom, the two foreign key columns in the adjacency list table (PartStructue in my file) would reference the primary key of the topmost level (all components).  The value of this primary key would of course be that of the corresponding foreign key in each sub-type of the type, and of sub-types of that sub-type, and so on down the line. These foreign keys would also be the primary keys of their respective tables.  The attributes common to all components, including the stock situation, would be represented by columns in the topmost table, while those specific to a sub-type, and not shared by other subtypes would be represented by columns in the relevant sub-type tables.

Hi All and many thanks for taking the time to reply.

I guess the closest I can get to what I am exactly looking for would be the RS Online catalogue of components, but then an offline version for my own stock of components

In this, if you choose "All Products" from the menu bar you can select the type of product category you are looking for. In my case, this could be "Passive Components".

From there, you can select a further breakdown of the type of passive component(s) you are looking for. Let's say "Fixed Resistors" for the purposes of this discussion. This takes you to the "Fixed Resistors Page" where you can further choose what type of fixed resistor(s) you are looking for.

Each fixed resistor type in this list, as an example, has common variables that are the same (manufacturer, resistance value, etc.) but also unique variables for its own type of resistor that other fixed resistors might not have / require.

The above is my primary goal in the database to catalogue a big ex-stock clearance I will soon receive, so that I know what I have got, quantities, etc.

Does this make more sense?

One idea would be to use real shopping cart software with a SQL Server back-end. You can then connect to the BE using Access if you want to do things that are not provided by the shopping cart itself.

I bet you could find some free or cheap software to get started with. One search term I used is "asp.net shopping cart solutions".

Hi Tom,

Sounds expensive and would not give me exactly what I wanted for my own situation.

I think I have made some progress anyway :

I have 3 tables that are related: Inventory, Main Category and Sub Category, where:

• Inventory is the top level component types (Passive, Conductors, Batteries, etc.)
• Main category lists all the components under each of the top level components in Inventory Table
• Sub Category lists all the components under each main category in Main Category table

So, when I add these to a form I can now choose items from 3 cascading combo-boxes and they all work (following a bit of macro and query building)

What I am stuck on is the next level down, the components themselves. I don't know whether I should have 1 single table with all possible fields in for all types of components, or 1 table per component type with fields in that table for that type of component only

Any advice or being pointed in the right direction would be greatly received!

In this, if you choose "All Products" from the menu bar you can select the type of product category you are looking for. In my case, this could be "Passive Components".

From there, you can select a further breakdown of the type of passive component(s) you are looking for. Let's say "Fixed Resistors" for the purposes of this discussion. This takes you to the "Fixed Resistors Page" where you can further choose what type of fixed resistor(s) you are looking for.

For doing that the structure would be a tree-structured hierarchy, e.g. in a very simple model:

Categories
….CategoryID  (PK)
….Category

Subcategories
….SubcategoryID  (PK)
….Subcategory
….CategoryID  (FK)

Components
….ComponentID  (PK)
….Component
….SubcategoryID  (FK)

Note that the referencing table Components at the bottom of the tree references only that level in the hierarchy immediately above it.  In the above simple example it does not need to reference Categories as the category is functionally determined by the subcategory, so to reference both would introduce redundancy and the risk of update anomalies.

You can then query the database in a top-down way by means of correlated combo boxes, in which the list of a subcategories combo box would be restricted to those which are subcategories of the selected category, and the components combo box would similarly be restricted to those which match the selected subcategory.  You'll find examples in ComboDemo.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to amend the form design accordingly.  

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

This little demo file illustrates the use of correlated combo boxes, both for data entry and for querying the database, using the local administrative areas of counties, districts and civil parishes in my neck of the woods.

This model can be combined with a types/subtypes model as described earlier, the Components table being the topmost type in the model, and with an adjacency list table to model a bill of materials, the primary key of Components being referenced by the two foreign keys in the adjacency list table.

I think my reply to your earlier post addresses these points.   I had not seen your latest post when I sent it.

Hi Ken and thanks for your reply.

Your database does indeed go part way to what I am looking to try and do. However, I was already that far :) I'm really just stuck on the lowest level of data table.

Imagine tens of thousands of different electronics components, each with their own specific attributes, but all of them have some attribute fields the same (e.g. manufacturer, price, etc.) However, many of the component attributes differ (e.g. surface mount resistors do not have the "pin length" attribute / field as they have no pins, but Through Hole Resistors do).

My issue is I do not know whether I should put all of the components in one big table with every possible field for every type of component, with some fields empty for some components (as they are irrelevant for those components), or, I should create a table per component type.

Make sense?