Machinery Component Database (1 Viewer)

jigaw_gd

New member
Local time
Today, 16:44
Joined
Jun 20, 2019
Messages
3
Hi All

Just wondering if someone could give me an idea of how to structure a machinery component (asset / maintenance) database. I'm struggling with machinery having lots of components, which has lots of components, each of which have components :banghead: all which have to be tracked and recorded based on time. (E.g. car, with four wheels, which have nuts, hubcap, tyres - would want to know which wheel the nuts belonged to, and when they were lasted changed out - this is a simplified example of what I am talking about).

I just can't picture what the structure should look like. Any input would be appreciated.

Thanks
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 18:44
Joined
Jan 20, 2009
Messages
11,938
Everything goes into one Parts table from the very smallest components, through subassemblies to completed products. Everything has a PartID and a PartDescription.

Another Assemblies table with three fields stores what goes into what. One field is the AssemblyID, another holds the PartIDs that go into it and the third has the quantity of each of those parts. One record for each part used for each assembly.

The Assemblies table has two relationships to the Parts table. The Parts table is treated like it is two tables. One-to-many relationship goes between the PartID of the Parts table and the AssemblyID of the Assemblies table. A many-to-many relationship between the PartID of the Assemblies table and the PartID of the second instance of the Parts table.

It sounds confusing but it isn't really. Just remember you are working with two instances of the same Parts table. One instance refers to the Assembly and the other is the Parts in an assembly. An assembly become the parts for the next level of assemblies.

Purchase and Sales tables store bought and sold. A Production table stores how many assemblies are made in each batch. In some cases all these tables can be combined into one with a field to indicate Purchase, Sale, Production, Adjustment (for lost or damaged etc). Store the values of the parts here rather than the Parts table. This way every purchase or production batch can have a different value.

Avoid storing the stock quantities of Parts. Use the Purchase, Sales and Production table/s to calculate the number of parts you have as and when you need to know it.
 
Last edited:

Galaxiom

Super Moderator
Staff member
Local time
Today, 18:44
Joined
Jan 20, 2009
Messages
11,938
I just reread your question and realised I missed the mark. The principles are similar to what I posted though, just more complex. Probably getting your head around that description first will prepare you for the next level.

Assets that need to be serialised each have a record in an Assets table rather than being treated as indistinguishable Parts. These have a relationship to the Parts table because the multiple Assets would share some identical characteristics such as a GeneralDescription and PartID.

I would probably have a SerialisedAssembly table to hold Assets that are assembled into other Assets. Then you can have a GeneralParts table that just has the quantities of unserialsed parts in an Assembly or an Asset as appropriate.

Think long and hard about the structure and post back with your plans. It is essential to get this right. The dual joins to the Parts and Assets tables are the keys to this.
 

jigaw_gd

New member
Local time
Today, 16:44
Joined
Jun 20, 2019
Messages
3
Galaxiom, thank you for your response, and apologies for delay in replying. Would you know of an off the shelf system that could be customised for our needs, or a developer who would be interested in doing this. It is quite a big job, and not my main role, and you're right, we need to get the structure correct from the beginning. (Apologies if I'm breaking any rules asking for this).
 

The_Doc_Man

Happy Retired Curmudgeon
Local time
Today, 03:44
Joined
Feb 28, 2001
Messages
16,837
Asking if we know of something isn't breaking any rules. Asking for a developer who is interested isn't breaking a rules. Just conduct any business via e-mail and/or other contact methods that don't clutter up the forum with what is essentially "personal" business. But it isn't wrong to look for willing contractors.

That said, I don't personally know of any such packages and don't take on that kind of work. These days I'm more into my writing hobby.
 

jdraw

Super Moderator
Staff member
Local time
Today, 04:44
Joined
Jan 23, 2006
Messages
13,123
You may get some ideas from this link to Hierarchical data structure.

Also, this thread has some relevant dialog re parts and assemblies that may provide some insight.

I don't know of any commercial product to do what you seek either.

Good luck.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 04:44
Joined
May 21, 2018
Messages
3,155
If you have a very few levels (less than 5) and a known structure, maybe you can do something like Glaxiom describes, but any real world implementation will require hierarchical self referencing data. I built a lot of these types of DBs. The data structure is very simple as Jdraw has pointed out. The actual implementation in Access on the other hand is pretty tricky. Access SQL unlike T-SQL has no native way to handle hierarchical data. The GUI, code, and SQL can get real involved requiring recursive code.

Basically, you will have a single Parts table and in that table is a foreign key to the parent record in the same table. So if the car ID is 1 then the wheels all have a parent id of 1. If a lug nut belongs to a wheel with ID of 2 then its parent id is 2. To view and interact with the data requires a Treeview, and access with its primitive GUI does not have a native Treeview. There is an OCX for 32 bit, but not supported in 64. I have been using a freeware MSFORMs based solution.

So your table would look like this. (It is purposely sorted out of entry order). Simply notice that the highest level does not have a parent ID and every other part has a parent ID.

Code:
partID	parentID	partSerial	description	partType
306	288	vvv-7890	5 L Predator V8	Engine
288		abc-123	Car	Main
302	309	NA	lug nut series 89	Lug Nut
298	308	NA	lug nut series 89	Lug Nut
299	308	NA	lug nut series 89	Lug Nut
301	308	NA	lug nut series 89	Lug Nut
303	309	NA	lug nut series 89	Lug Nut
304	309	NA	lug nut series 89	Lug Nut
305	309	NA	lug nut series 89	Lug Nut
300	308	NA	lug nut series 89	Lug Nut
289	308	xxx-123	Michelin All Terrain	Tire
292	311	xxx-126	Michelin All Terrain	Tire
291	310	xxx-125	Michelin All Terrain	Tire
290	309	xxx-124	Michelin All Terrain	Tire
297	311	zzz-459	Sport Series Hubcap	Hubcap
296	310	zzz-458	Sport Series Hubcap	Hubcap
294	309	zzz-457	Sport Series Hubcap	Hubcap
293	308	zzz-456	Sport Series Hubcap	Hubcap
308	307	rrr-123	Tech 7 Black	Wheel
309	307	rrr-345	Tech 7 Black	Wheel
310	307	rrr-456	Tech 7 Black	Wheel
311	307	rrr-789	Tech 7 Black	Wheel
307	288	NA	Wheels qty 4	Wheels
Now I would have a tree view like this as my GUI.



Normally to add a new part I double click a parent node and pop up a new record form. When you close the form it shows up as a child to that parent. I am not a commercial developer but more than capable of doing this, you can PM me to discuss.
 

Attachments

Galaxiom

Super Moderator
Staff member
Local time
Today, 18:44
Joined
Jan 20, 2009
Messages
11,938
Code:
partID    parentID    partSerial    description    partType
306    288    vvv-7890    5 L Predator V8    Engine
288        abc-123    Car    Main
302    309    NA    lug nut series 89    Lug Nut
298    308    NA    lug nut series 89    Lug Nut
299    308    NA    lug nut series 89    Lug Nut
301    308    NA    lug nut series 89    Lug Nut
303    309    NA    lug nut series 89    Lug Nut
304    309    NA    lug nut series 89    Lug Nut
305    309    NA    lug nut series 89    Lug Nut
300    308    NA    lug nut series 89    Lug Nut
289    308    xxx-123    Michelin All Terrain    Tire
292    311    xxx-126    Michelin All Terrain    Tire
291    310    xxx-125    Michelin All Terrain    Tire
290    309    xxx-124    Michelin All Terrain    Tire
297    311    zzz-459    Sport Series Hubcap    Hubcap
296    310    zzz-458    Sport Series Hubcap    Hubcap
294    309    zzz-457    Sport Series Hubcap    Hubcap
293    308    zzz-456    Sport Series Hubcap    Hubcap
308    307    rrr-123    Tech 7 Black    Wheel
309    307    rrr-345    Tech 7 Black    Wheel
310    307    rrr-456    Tech 7 Black    Wheel
311    307    rrr-789    Tech 7 Black    Wheel
307    288    NA    Wheels qty 4    Wheels
You have a lot of repeating data in there.

The serialization information and fundamental part record should be separated out.
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 18:44
Joined
Jan 20, 2009
Messages
11,938
If you have a very few levels (less than 5) and a known structure, maybe you can do something like Glaxiom describes, but any real world implementation will require hierarchical self referencing data.
What I described is a self referencing structure. It is just that the relationship information is held in a separate table that can support relationships that don't demand serializing every single individual component.

I don't see any reason why it would be have the limitations you suggest such as a the number levels.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 04:44
Joined
May 21, 2018
Messages
3,155
Code:
You have a lot of repeating data in there.
The serialization information and fundamental part record should be separated out.
Yes in truth there would be a lot more tables, but want it to be simple to explain. So all "generic Parts" are probably first in there own table. So my real table looks more like a bunch of keys
PartID ParentID GenericPart_ID S/N and Fields Unique to that specific part

What I described is a self referencing structure. It is just that the relationship information is held in a separate table that can support relationships that don't demand serializing every single individual component.
Now that I reread it, I think I got what you are saying especially if it is for unserialized items. Can you do a simple example to clarify?
 

jigaw_gd

New member
Local time
Today, 16:44
Joined
Jun 20, 2019
Messages
3
Thanks guys for your input. Still looking at this. However, for some reason I'm not able to reply to messages in my inbox?!?
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom