Go Back   Access World Forums > Microsoft Access Discussion > Theory and practice of database design

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-19-2019, 11:14 PM   #1
jigaw_gd
Newly Registered User
 
Join Date: Jun 2019
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
jigaw_gd is on a distinguished road
Machinery Component Database

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 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

jigaw_gd is offline   Reply With Quote
Old 06-20-2019, 03:52 AM   #2
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,607
Thanks: 89
Thanked 1,492 Times in 1,408 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Machinery Component Database

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 by Galaxiom; 06-20-2019 at 04:01 AM.
Galaxiom is offline   Reply With Quote
Old 06-20-2019, 04:16 AM   #3
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,607
Thanks: 89
Thanked 1,492 Times in 1,408 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Machinery Component Database

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.

Galaxiom is offline   Reply With Quote
Old 07-10-2019, 10:55 PM   #4
jigaw_gd
Newly Registered User
 
Join Date: Jun 2019
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
jigaw_gd is on a distinguished road
Re: Machinery Component Database

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).
jigaw_gd is offline   Reply With Quote
Old 07-11-2019, 06:13 AM   #5
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,363
Thanks: 87
Thanked 1,644 Times in 1,526 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Machinery Component Database

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.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 07-11-2019, 06:44 AM   #6
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,142
Thanks: 81
Thanked 2,010 Times in 1,958 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: Machinery Component Database

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.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
Old 07-11-2019, 03:39 PM   #7
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,790
Thanks: 34
Thanked 540 Times in 513 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: Machinery Component Database

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.
Attached Images
File Type: jpg Treeview.jpg (51.3 KB, 67 views)

MajP is offline   Reply With Quote
Old 07-11-2019, 04:08 PM   #8
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,607
Thanks: 89
Thanked 1,492 Times in 1,408 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Machinery Component Database

Quote:
Originally Posted by MajP View Post

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 is offline   Reply With Quote
Old 07-11-2019, 04:21 PM   #9
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,607
Thanks: 89
Thanked 1,492 Times in 1,408 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Machinery Component Database

Quote:
Originally Posted by MajP View Post
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.
Galaxiom is offline   Reply With Quote
Old 07-11-2019, 04:52 PM   #10
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,790
Thanks: 34
Thanked 540 Times in 513 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: Machinery Component Database

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

Quote:
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?
MajP is offline   Reply With Quote
Old 07-24-2019, 06:32 PM   #11
jigaw_gd
Newly Registered User
 
Join Date: Jun 2019
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
jigaw_gd is on a distinguished road
Re: Machinery Component Database

Thanks guys for your input. Still looking at this. However, for some reason I'm not able to reply to messages in my inbox?!?

jigaw_gd is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Database Design for Tracking Component domingsun General 3 12-04-2015 06:09 AM
429-ActiveX component can't create component - Email Code cl8 Modules & VBA 1 06-06-2011 12:47 AM
Excel 11 Component c19h28O2 Forms 0 02-21-2008 02:18 AM
web component cannot open Access database glee Reports 0 01-03-2006 10:38 AM




All times are GMT -8. The time now is 05:32 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World