Equipment Hierarchies 3

[mitch37]

Hello guys

I am an Industrial Engineer (In my first year of work) and I have obviously not done much databse programming. I am designing a database for a client that stores equipment data and failures. The equipment has a 'hierarchy' and cascades down from the line/area down to the smallest component, with a set naming convention.

Bearing in mind that I use MS Access with minimal VB code (i use the built in functions), is there any way i can store data so that I can draw a report on the lowest level and it books it links it to the highest level that it is connected to?

I have added what has been done so far, even though it is slightly confidential...but it does not have data in it so I guess it is ok.

 

[MajP]

I am more concerned about what we are not seeing because that will drive how you set up some of these tables, primary keys, and relationships.

Really need to know if you plan to account for multiple plants, and multiple sections. This will be easy to account for ahead of time, but could be very difficult later.

For example if the line number is unique to any line, in any section in any plant then you are good to go. But if you do hope to account for multiple plants and sections and they reuse these numbers then you need a little different strategy. You can use a composite key, auto number, or some other natural key. I assumed lines have a unique number, but I pulled this from the equipment number. I am not sure if this is correct.

My assumption is that you have many plants, plants are divided into sections, (maybe domestic production, foreign production) and sections have lines, lines have major pieces of equipment, equipment has componenents.

If that is true I would design to that.


tblPlants
plantName (PK)
plantFields
otherPlantFields
I would think plantName is a good PK unless names change often. Then you may want something else.

Now if all plants have similar sections then sectionName may not be a good key. You can make a composite key by using both the plantName and sectionName as PKs, or use an auto, or if there is something else unique to a section.

tblSection
sectionID (PK)
sectionName
otherSection fields (for that specific section in that plant)

or you can make a composite
tblSection
sectionName (pk)
plantName (pk)
(personally I do not use a lot of composites, because they are a little tricky to work with. However, they make a more natural relation)

So tblLine depends on the above. If all lines relate to a section then you need a foreign key to the section. If you use a composite primary key then you need a composite foreign key (this is where it gets hard). I will assume you just have a unique sectionID.

tblLine
lineNumber (PK unique to a line, in any section in any plant)
lineName
other line fields
sectionID_fk (foreign key to a section)

I see you gave the major pieces of equipment an ID. If that is the case then you really do not need the long names any more. Instead of Blender Line 1 you can now have many
1234 Blender
2345 Blender
6789 Blender

and we know by the foreign key relations which line, section, and plant they belong.

I do not see any problem with your relation from components to equipment. The equipment ID is a pk, and the equipmentID in the component table is the foreign key. But my guess is you know have to get those equipmentIDs into your component table. If Blender Line 1 now has an ID of xyz123 you need to figure out how to get xyz123 in your component table in the fields equipmentID_fk for the related items. This probably can be done with an update query.

Lets figure these out first then we can deal with the other tables.

One thing. Are failures always tagged at the component level? Are failures tagged against multiple components? Are failures tagged at higher levels? In other words can a failure be tagged at the equipment level without picking a component? These are buisness rules. If you always tag them at component level then you know everything about higher levels as well. You can handle all of these cases, but they will drive your table design.

Failures should not have an action ID foreign key. There are multiple actions for a failure. So the relation is the other way. This allows for multiple actions against a given failure.

tblActions
actionID (pk)
failureID_fk
failureTitle
memoFailureDescription
failureType
other failure fields.

 

[MajP]

Mitch and others,
I started a new thread in the Tables forum to continue this thread. It is thread700-1539884. So just start posting in there instead of this thread. For some reason this post would not show up in my thread minder, and it was getting to be a pain to scroll the 60 replies. When threads get that long other people have a tendency not to join the conversation, because they do not want to read all the background. However, you are kind of at a clean start now. Also the thread has become a pure table question.

 

[mitch37]

'''My assumption is that you have many plants, plants are divided into sections, (maybe domestic production, foreign production) and sections have lines, lines have major pieces of equipment, equipment has componenents.''''

I think it may be best to design for many plants, even if each plant may usea new version of the database, because, like you said, they may want to use one database in future. The original 'hierarchy' was given to me as o non-concrete hierarchy and actually went LINE - EQUIPMENT - PARTITIONING - ASSEMBLY - PARTS. from what i have seen from visits to the plant, i think line - section - equipment - component would be easier to understand.

"One thing. Are failures always tagged at the component level?' - They should be, as this should be done during root cause analysis. However, at times, the failure may be booked to a piece of equipment (higher level) if not done correctly.

Are failures tagged against multiple components? - They can be.

These are business rules. If you always tag them at component level then you know everything about higher levels as well. You can handle all of these cases, but they will drive your table design.' - Perhaps if the database forces the failure to be tagged at component level, it will drive root cause analysis?

will edit the tables a bit tonight and post the new details.


By the way, you seem to enjoy this a lot!?!

 

[MajP]

See my reply on the new post.