Nested Levels / Containers Equipment Inventory Database

[Rod]

DG,

I found some time!

As I reread your post I was reminded of a military logistics project I was once involved in whereby the military required preassembled kits of spares to be ready for instant deployment. Their spares were housed in containers, one container for each type of hardware; yours are stored in bins. This brings me immediately to what is slightly different (or unorthodox) about these situations: there is a list of parts that you expect to store in each bin whether or not you physically have such a part in stock. In other words each bin has a template (boilerplate) of parts that would normally be kept there. You may even have a minimum stock quantity for each of these parts. Hence by interrogating this information you can derive a list of parts to order to keep the bin stock at the desired levels.

Conversely is it possible that you may have a part in stock for a particular vehicle that does not appear on the template? I would suggest that this occurs from time to time and so your system should cater for this.

Before pulling the rabbit out of the hat I need to ask some questions and confirm some assumptions. When I refer to entities I am defining them as applied to your situation, not the wide world.

• Do you have experience of relational databases? The answer will help me pitch my replies at the correct level. I don’t want to insult you by teaching you to suck eggs, as we say. You obviously have experience of building Access tables and as you have included a meaningless key in some of them, i suspect you are not new to relational databases.
• Do you have experience of SQL?
• A bin is dedicated to a vehicle.
  
  • Is it possible now or in the future that a bin will contain parts for more than one vehicle? What will happen if you have two identical lawnmowers? Do they share a bin?
  • Is it possible now or in the future that the parts for a vehicle will be spread across more than one bin?
  
  
• Racks and shelves are of no interest to you except as means for finding the location of a bin. What I mean here, for example, is that you are not interested in the dimensions of a shelf or the load-bearing capacity of a rack. The reason I ask is that if I am right, there is no overriding reason to model them as tables; you may very well choose to do so for a number of reasons but if they do not ‘attract’ their own data (i.e. key only) then you could dispense with the tables.
• Following on from above, how important is rack type? Is it just a housekeeping device?
• You use both the term vendor and the term supplier for what I believe is the same thing. I shall use the term supplier.
• A part (synonym for spare part) may be bought from more than one supplier and may be of differing brand.
• You are not interested in this system of monitoring which suppliers can supply which part/brand combinations.
• You are not interested in this system of monitoring the possible brands for each part.
• You are interested in dividing the parts into different categories (synonym for spare part category).
• A part does not define a physical item (a specification) but rather a generic spare. When ordering, of course, you must add the vehicle information for which the part is intended.

That’s it for the initial interrogation. You have Access v2010 while I remain at v2007. There’s no difficulty in me sending you stuff but there might be difficulties the other way around.

[dgaletar]

Hey Rod, this is awesome stuff! Here is some more info:

Do you have experience of relational databases? The answer will help me pitch my replies at the correct level. I don’t want to insult you by teaching you to suck eggs, as we say. You obviously have experience of building Access tables and as you have included a meaningless key in some of them, i suspect you are not new to relational databases.

As I say above, I (feel that I) work at an "Advanced Beginner's" pace. While I've never really had any official training, I have built a few relational databases in the past. The problem is that I usually begin with a basic working model, and then go in and make changes according to our needs. I seem to really be having trouble finding anything like this one though.

Also, I have been working on a small "side" project of building a database from scratch... just to see how it goes. And, well, it's not going all that well. I tried following a MS Word document that I came across in a past post titled "Entity-Relationship Diagramming" by Roger J. Carlson (You can find his blog here). While it was REALLY helpful, it only took me so far, and I haven't been able to get past that point. (Another project for another day! )

The database that we are talking about here, though, is a "work related" project that will ultimately make my life a lot easier. Therefore I need it to be done right, and relatively soon. In other words, I don't have the time to play with it for a month or two while I grasp the concepts... if that makes sense.

Do you have experience of SQL?

Also as stated above, probably at an "Advanced Beginners" level. The same goes for VBA. I understand "Option Compare Database" & "Option Explicit", for the most part anyway. Mostly, though, my experience with SQL & VBA is editing an existing code.

A bin is dedicated to a vehicle.

Yes.

Is it possible now or in the future that a bin will contain parts for more than one vehicle? What will happen if you have two identical lawnmowers? Do they share a bin?

Will never happen. A unique "CUA#" is given to each and every vehicle or piece of equipment that we service.

Is it possible now or in the future that the parts for a vehicle will be spread across more than one bin?

No. The purpose of the bins is to eliminate the need to have parts anywhere but in the vehicle "bins" that they belong to. Now, with that being said, you do bring up ONE unique issue. We will have a handful of parts that we only have a few of that will belong to several vehicles.

An example would be batteries. While we will be keeping 3 each PN (Part Number) 65PG Batteries, there are about 12 vehicles that can use that part. It wouldn't make sense to keep 12 each of such a large item. Especially when we could end up with a "shelf life" issue. (In other words, a vehicle battery is designed to last, for arguments sake, say 3 years. If it sat on the shelf for two of those 3 years, we would have to replace it the following year. Not a very good use of the schools funds!)

Lastly, with all of that being said, if this raises an issue with the design or functionality of the database, skip it. As I stated, we only have a handful of these items.

Racks and shelves are of no interest to you except as means for finding the location of a bin.

You are absolutely correct! That information is strictly used to find the location of the parts for that vehicle.

Following on from above, how important is rack type? Is it just a housekeeping device?

Well, this is a little more important. You see, the goal is to have one rack to host the parts to all of the vehicles (pickup trucks, vans, buses, etc.), another rack for the mowers (of which we have three separate models that we currently use (1. Laser; 2. F2260; 3. Walk Behind)), another for "Snow Emergency" equipment, and one other for "Shop Supplies" (cleaning supplies and fluids (oil, transmission fluid, carburetor cleaner, hand soap, etc.)).

NOTE: While it would be nice to also track these supplies within this database, as with the batteries, if it causes any disruption in the database design, it is not necessary.

You use both the term vendor and the term supplier for what I believe is the same thing. I shall use the term supplier.

Actually, in this case, we will need both. The "Supplier" is the... NOPE! You are right. I just went back and checked and you are CORRECT! An error on my part. Thank you for catching that. The Supplier is the company that we would call to order the part(s).

A part (synonym for spare part) may be bought from more than one supplier and may be of differing brand.

Again you are correct. While we are striving to use only one "brand" for each item, and ultimately limit the number of suppliers that we use, I do not see that as possible... especially at this point.

Something that may also be noted here is that more than one supplier can carry the same brand. (example: "Hastings" filters may be bought from both Delcoline AND Parts Authority) And, with that being said, they may be different prices! Oh Crap! I just realized that. In other words, Hastings filter PN LF110 might be 1.09 from Delcoline, and cost $2.15 from Parts Authority. AAAARRRGGGHHH!!! Se that... my head is spinning again!

So I am guessing that what would make the difference is which part from which supplier was actually on the shelf (or recorded in my inventory)... right? In other words, if we had (6) each PN LF110 (from Parts Authority) and (4) each from Delcoline, we would potentially have (6 x $2.15 from Parts Authority and 4 x $1.09 from Delcoline - OR - $12.90 from Parts Authority & $4.36 from Delcoline - OR - $17.26 total) on the shelf for that part number?!?

You are not interested in this system of monitoring which suppliers can supply which part/brand combinations.

Not necessarily, but it doesn't sound like a bad thing to know...

You are not interested in this system of monitoring the possible brands for each part.

Again, not necessarily, but also doesn't sound like a bad thing to know...

You are interested in dividing the parts into different categories (synonym for spare part category).

Well, the "end goal" with this is to be able to see, for example, how many PN LF110 oil filters we use. Or to be able to print out a list of WHICH oil filters we are stocking. So, if that means that we need to divide the parts into categories, than, yes.

A part does not define a physical item (a specification) but rather a generic spare. When ordering, of course, you must add the vehicle information for which the part is intended.

Finally!!! You finally lost me. (That was an impressive list of questions by the way!!! Making my head hurt again...)

If I am understanding you correctly, I think that you are wrong. At least in my mind, a part does define a physical item. In other words, if we have 10 vehicles that use a Hastings oil filter numbered LF110, then we should have 10 LF110's on the shelves somewhere.

And, when ordering, we should not have to add the vehicle information for which the part is intended. That work was already done when the bins were created. So if I performed my weekly inventory (ie. counted what we have on the shelves right now), and we had only 1 LF110 oil filter, then I would call Parts Authority and say "please send me 9 LF110 oil filters". I would then get them, put them on the shelves, and our shelves would be fully stocked.

Allow me to explain further. To reuse an example that I used in my earlier post:

"Vehicles" Rack, "#2" Shelf, "#4" Bin belongs to "CUA# 103", and contains:

• Air Filter, Oil Filter, Wiper Blades, Front Brake Pads, Rear Brake Pads

Since we know that Vehicle (CUA#) 103 is a "2002 FORD Econoline Van with a 5.4L engine", we know exactly which parts fit that vehicle.

Therefore, if I were to go to the "Vehicles" Rack, find the "#2" Shelf and look into Bin "#4", I should see the following items (specific to that vehicle):

Part Type	Qty	Part #	Brand	Supplier	Unit	Cost
Air Filter	1	AF484	Hastings	Parts Authority	ea.	2.15
Oil Filter	1	LF110	Hastings	Parts Authority	ea.	1.09
Wiper Blades (DS)	1	22”	TRICO	Delcoline	ea.	2.11
Wiper Blades (PS)	1	19”	TRICO	Delcoline	ea.	1.95
Front Brake Pads	1	106.11940	Posi-Quiet	Parts Authority	pair	27.50
Rear Brake Pads	1	106.10920	Posi-Quiet	Parts Authority	pair	25.20

If any of those items were not there, I would simply call the respective Supplier and place an order for that/those part(s).




Lastly, on a separate note, while it is in no way necessary, it wouldn't be a bad idea to be able to keep a "unit cost" for each item. I guess that, someday, we might be interested in knowing just how much "Inventory Dollars" we are keeping here.

I am soooo excited to see how this all comes together!!! Thank you so much for all of the hard work!

DG

[Rod]

Hi DG,

First, as this is likely to prove an extensive interchange of lengthy posts, I’ve sent you my email address via a private message. After this post (or your acknowledgement) I suggest we communicate via email and avoid cluttering up this forum with very specific information.

Will never happen. A unique "CUA#" is given to each and every vehicle or piece of equipment that we service.
No. The purpose of the bins is to eliminate the need to have parts anywhere but in the vehicle "bins" that they belong to. Now, with that being said, you do bring up ONE unique issue. We will have a handful of parts that we only have a few of that will belong to several vehicles.

An example would be batteries. While we will be keeping 3 each PN (Part Number) 65PG Batteries, there are about 12 vehicles that can use that part. It wouldn't make sense to keep 12 each of such a large item. Especially when we could end up with a "shelf life" issue. (In other words, a vehicle battery is designed to last, for arguments sake, say 3 years. If it sat on the shelf for two of those 3 years, we would have to replace it the following year. Not a very good use of the schools funds!)

Lastly, with all of that being said, if this raises an issue with the design or functionality of the database, skip it. As I stated, we only have a handful of these items.

Yes, to cater for ‘common’ parts and supplies does complicate the design. I had in mind identical vehicles and parts common to a range of vehicles like brake pads – didn’t think of batteries. OK, you say only a handful of such items so It may be more efficient accept that the design will not cater for this and to find a work-around – perhaps create a ‘dummy’ vehicle or maybe build a common items/supplies add-on.

(Will never happen? As an anecdote I remember working on Data General equipment way back in the late 70s when error messages were only numbers because of storage constraints, etc. One night the system crashed giving an error number. When we looked up the number the explanation was: “This can never possibly happen.”)

If I am understanding you correctly, I think that you are wrong. At least in my mind, a part does define a physical item. In other words, if we have 10 vehicles that use a Hastings oil filter numbered LF110, then we should have 10 LF110's on the shelves somewhere.

Point taken.

Something that may also be noted here is that more than one supplier can carry the same brand. (example: "Hastings" filters may be bought from both Delcoline AND Parts Authority) And, with that being said, they may be different prices! Oh Crap! I just realized that. In other words, Hastings filter PN LF110 might be 1.09 from Delcoline, and cost $2.15 from Parts Authority. AAAARRRGGGHHH!!! Se that... my head is spinning again!

Choosing to cost your inventory by actual cost or standard cost will affect the design. If standard cost then the unit price is stored on the Part table; if actual cost then, as you realize, you cannot combine inventory items and need a relationship to the order by which they were obtained – more on this later. There is a technique of average cost but you really don’t want to get into that, trust me.

So to business, let’s get this rabbit part the way out of the hat. Here is a first cut ERD. Flying in the face of convention my arrows indicate a 0:M or 1:M relationship where M itself is interpreted as being either 0 to many or 1 to many – we don’t need to be more precise at the moment. I need to point out that the ERD contains only master (static) data and does not show entities for transactional data such as orders. Another point to make is that it is a logical, platform-independent design, the physical comes later after we have ‘proved’ the logical.

You need to sit with this diagram and determine whether it meets your needs. Easier said than done, I know. The entity descriptions below will help but mainly you must ask the questions: “How do I ….?” Remember that your user interface (forms) will be based on views (queries) rather than on raw tables. More on this later.

Rack Type	An id (pk) and a description.
Rack	An id (pk) and Rack Type as a foreign key.
Shelf	An id (pk) and Rack as a foreign key.
Bin	An id (pk) and Shelf and Vehicle CUA as foreign keys.
Bin Item	This entity contains information about the actual contents of a bin, whether the item is prescribed by a template or is an ad hoc. If actual costing is required then each item must be specified on a separate row and related to the order on which it was obtained (which will give the unit price prevailing at the time). If standard costing is used then identical items may be merged by means of a quantity attribute. An id (pk) and Part as foreign key. Bin Template is also a foreign key but could be null if the part is ad hoc. (Welcome to the wonderful world of nulls. Oops, I’ve just become physical.)
Bin Template	This entity describes the intended stock list for each vehicle. It does not presuppose a brand or manufacturer but simply nominates a part type (see part type) and perhaps quantity. An id (pk) and Bin and Part Type as foreign keys. The vehicle (CUA) is found by tracing back through Bin.
Part Type	This describes the classification of parts in sufficient granularity to support the function of Bin Template. For example to nominate ‘Belt’ as a Part Type is too broad. Instead there should be ‘Timing Belt,’ ‘Fan Belt,’ etc. There is no brand or manufacturer connotation with the information in this entity. An id (pk) and description.
Part	A specific branded spare part. The data in this entity is likely to be fairly volatile. An id (pk) and Part Type and Brand as foreign keys. For ad hoc spare parts the Part Type key could be null indicating the lack of a classification. (Again I’m being physical.)
Brand	Simply (at the moment) a way of indexing Parts. In future enhancements this entity could relate to which Suppliers offer which Brands of which Part Types. An id (pk) and a description
Supplier	All your Supplier data. . In future enhancements this entity could relate to which Suppliers offer which Brands of which Part Types. An id (pk) plus all your data.

[dgaletar]

I had in mind identical vehicles and parts common to a range of vehicles (like brake pads)...

99.9% accurate. The "Battery" example would be the exception.

It may be more efficient accept that the design will not cater for this and to find a work-around – perhaps create a ‘dummy’ vehicle or maybe build a common items/supplies add-on.

I think that this is a very good idea. Actually, a GREAT idea. You see, all of the identical PN's will be in one bin. In other words, "Rack Type" D, "Rack" 2, "Shelf" 3, "Bin" 5 should have (3) 65PG (batteries) in it. It could be something like "Vehicle (or CUA#) BAT1"... or something like that. Right!?! And the next "group battery" could be "CUA# BAT2" and be located in "Rack Type" D, "Rack" 2, "Shelf" 3, "Bin" 6 and have (3) 78DTPG (batteries) in it, and so on. "Rack Type" D, "Rack" 2, "Shelf" 4, "Bin" 1 could have (12) KE-1060B (Electrical Contact Cleaner Spray) in it, while "Rack Type" D, "Rack" 2, "Shelf" 4, "Bin" 2 could have (12) WD-40 (Spray Lubricant) in it.

Choosing to cost your inventory by actual cost or standard cost will affect the design.

Again, either way works fine for our use. Remember that this is just a way of giving us an idea of what (dollar wise) is on the shelves.

...does not show entities for transactional data such as orders.

No desire to put "Orders" or "Received" counts in. End goal is to simply count what we physically have and compare it to what we should actually have, thus creating a list for ordering purposes.

You need to sit with this diagram and determine whether it meets your needs.

The answer is... almost... I think. The only thing that I don't see is a way to actually "Count" the "Actual On Hand" quantity of items in a bin and compare it to the (and I'm guessing here) "Bin Item" count. In other words, as I read it, the "Bin Item" would basically be saying that, for example, "Rack Type" A, "Rack" 1, "Shelf" 3, "Bin" 4 should have (1) ea. LF110, (1) ea. AF484, and (2) ea. 22" Wiper Blade.

What I am not seeing is a way for me to say that it ACTUALLY has (0) ea. LF110, (1) ea. AF484, and (1) ea. 22" Wiper Blade, thus telling me that I am in need of ordering (1) ea. LF110, (0) ea. AF484, and (1) ea. 22" Wiper Blade.

Does that make sense?

[Rod]

I shall answer by email. See you in another dimension.

[dgaletar]

You got it!!!

[Rod]

David,

For you:

DG.zip (128.3 KB)

Rod