Equipment Failure Tracking Database 1

[MajP]

Mitch,
I have created a new thread from your thread702-1516577. Your thread is now really a table design question/s. More importantly for some reason that post does not show up in my "My Replies", and it was getting painfully long.

-----------------------------------------------------------
here is the documentation:

DocumentTables

tblAction
ActionID_pk dbLong PrimaryKey Indexed
Action01 dbText
Action02 dbText
Action03 dbText
Action04 dbText
Action05 dbText
ActionDescription dbMemo
FailureID_fk dbLong ForiegnKey Indexed

tblComponents
ComponentNumber_pk dbLong PrimaryKey Indexed
ComponentName dbText
EquipID_fk dbLong ForiegnKey Indexed
ParentComponentID dbLong Indexed

tblEquipment
EquipID_pk dbLong PrimaryKey Indexed
EquipName_pk dbText
FunctionalLocation dbText
LineNumber_fk dbLong ForiegnKey Indexed

tblFailures
FailureID_pk dbLong PrimaryKey Indexed
DateofFailure dbDate
TimeofFailure dbText
FailureCategory dbText
PersonID_fk dbLong ForiegnKey Indexed
LineNumber_fk dbLong ForiegnKey Indexed
EquipID_fk dbLong Indexed
ComponentNumber_fk dbLong ForiegnKey Indexed
ActionID_fk dbLong Indexed

tblLines
LineNumber_pk dbLong PrimaryKey Indexed
FunctionalLocation_fk dbText Indexed

tblPerson
PersonID_pk dbLong PrimaryKey Indexed
Name dbText
Surname dbText
Position dbText

tblTeamMembers
TeamID_pk dbLong PrimaryKey Indexed
TeamDescription dbMemo
PersonID_fk dbLong ForiegnKey Indexed


DocumentRelations

Name: tblComponentstblFailures
Table: tblComponents
Foreign Table: tblFailures
PK: ComponentNumber_pk FK:ComponentNumber_fk

Name: tblEquipmenttblComponents
Table: tblEquipment
Foreign Table: tblComponents
PK: EquipID_pk FK:EquipID_fk

Name: tblFailurestblAction
Table: tblFailures
Foreign Table: tblAction
PK: FailureID_pk FK:FailureID_fk

Name: tblLinestblEquipment
Table: tblLines
Foreign Table: tblEquipment
PK: LineNumber_pk FK:LineNumber_fk

Name: tblLinestblFailures
Table: tblLines
Foreign Table: tblFailures
PK: LineNumber_pk FK:LineNumber_fk

Name: tblPersontblFailures
Table: tblPerson
Foreign Table: tblFailures
PK: PersonID_pk FKersonID_fk

Name: tblPersontblTeamMembers
Table: tblPerson
Foreign Table: tblTeamMembers
PK: PersonID_pk FKersonID_fk

 

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

If your buisness rules allow you to force users to tag against one or more components and not at higher levels then that is good because this gets a little complicated just by the fact you need to associate it with many components.

You need to create a many to many table

tblFailures_Components
failureID_fk (foreign key to tblFailures)
componentID_fk (foreign key to tblComponent)

your data in this table looks like
123 abc
123 cde
123 ghi
234 abc
234 qrt

so failure 123 was associated to parts abc, cde, ghi
failure 234 was assocaited to parts abc, qrt
and then using a query you can now return everything about these parts, the failure, the actions, the equipment, the line, the section and the plant.

then tblFailures becomes simply

FailureID_pk dbLong PrimaryKey Indexed
dtmDateTimeOfFailure dbDate
FailureCategory dbText
PersonID_fk dbLong ForiegnKey Indexed

I would make date and time a single field because a date field has a date and time component. It will make searching and reporting far easier. Time as a text field would be bad.

"By the way, you seem to enjoy this a lot!?!"
I am a test engineer not a database designer. This is right up my alley. I am currently working a large db for test failures for a software system.

 

[mitch37]

DocumentTables

tblAction
ActionID_pk dbLong PrimaryKey Indexed
Action01 dbText
Action02 dbText
Action03 dbText
Action04 dbText
Action05 dbText
ActionDescription dbMemo
FailureID_fk dbLong ForiegnKey Indexed

tblComponents
ComponentNumber_pk dbLong PrimaryKey Indexed
ComponentName dbText
EquipID_fk dbLong ForiegnKey Indexed
ParentComponentID dbLong Indexed

tblEquipment
EquipID_pk dbLong PrimaryKey Indexed
EquipName_pk dbText
FunctionalLocation dbText
LineNumber_fk dbLong ForiegnKey Indexed

tblFailures
FailureID_pk dbLong PrimaryKey Indexed
dtmDateTimeofFailure dbDate
FailureCategory dbText
FailureDuration (mins) dbLong
PersonID_fk dbLong ForiegnKey Indexed

tblFailures_Components
FailureCompID dbLong PrimaryKey Indexed
FailureID_fk dbLong ForiegnKey Indexed
ComponentNumber_fk dbLong ForiegnKey Indexed

tblLines
LineNumber_pk dbLong PrimaryKey Indexed
FunctionalLocation_fk dbText Indexed
SectionID_fk dbLong ForiegnKey Indexed

tblPerson
PersonID_pk dbLong PrimaryKey Indexed
Name dbText
Surname dbText
Position dbText

tblPlants
PlantName_pk dbText PrimaryKey Indexed
PlantDescription dbMemo

tblSections
SectionID_pk dbLong PrimaryKey Indexed
SectionName dbText Indexed
SectionDescription dbMemo
PlantName_fk dbText ForiegnKey Indexed

tblTeamMembers
TeamID_pk dbLong PrimaryKey Indexed
TeamDescription dbMemo
PersonID_fk dbLong ForiegnKey Indexed

 

[mitch37]

DocumentRelations

Name: tblComponentstblFailures_Components
Table: tblComponents
Foreign Table: tblFailures_Components
PK: ComponentNumber_pk FK:ComponentNumber_fk

Name: tblEquipmenttblComponents
Table: tblEquipment
Foreign Table: tblComponents
PK: EquipID_pk FK:EquipID_fk

Name: tblFailurestblAction
Table: tblFailures
Foreign Table: tblAction
PK: FailureID_pk FK:FailureID_fk

Name: tblFailurestblFailures_Components
Table: tblFailures
Foreign Table: tblFailures_Components
PK: FailureID_pk FK:FailureID_fk

Name: tblLinestblEquipment
Table: tblLines
Foreign Table: tblEquipment
PK: LineNumber_pk FK:LineNumber_fk

Name: tblPersontblFailures
Table: tblPerson
Foreign Table: tblFailures
PK: PersonID_pk FKersonID_fk

Name: tblPersontblTeamMembers
Table: tblPerson
Foreign Table: tblTeamMembers
PK: PersonID_pk FKersonID_fk

Name: tblPlantstblSections
Table: tblPlants
Foreign Table: tblSections
PK: PlantName_pk FKlantName_fk

Name: tblSectionstblLines
Table: tblSections
Foreign Table: tblLines
PK: SectionID_pk FK:SectionID_fk

 

[mitch37]

The above is my new relationships. Do you think it is sufficient? I am a little worried about 1 to 1 relationships that seem to be present, but i am keen to restart on forms. do not want to jump the gun, because this database must function properly.

 

[MajP]

Most look good to me. I am Not certain about actions.
tblAction
ActionID_pk dbLong PrimaryKey Indexed
Action01 dbText
Action02 dbText
Action03 dbText
Action04 dbText
Action05 dbText
ActionDescription dbMemo
FailureID_fk dbLong ForiegnKey Indexed

What is Action01 to Action05? This does not look normalized unless every action has these 5 characteristics. The action and failure table look correct where an action has a fk relating it to a failure. That way a failure can have multiple actions associated with it. However this does not seem to be what the relationship in the documenter says. You probably changed the table after you documented the relations. The relations looks like you used to have it where a failure had a action fk. That would only allow a failure to have 1 action.

What is your concern about 1:1 relations? There are none. Any table that has a fk relating it to another table means you can put many records in that table with the same fk and thus a 1:many.
All relations are 1:many except failures and components have a many:many. Do you think you have other many:many we need to account for? You might depending on your rules.

Teams are not done correctly. If a person can be on only one team then the tblPersons should have a fk to tblTeams. However if a person can be on more than one team (many:many) then you need another junction table.

tblTeam
teamID
teamName (this could possibly be the PK)
teamDescription
other team fields

tblPersons_Teams
personID_fk
teamID_fk

then if person 1 is on team a,b,c. And person 2 is on c,d,e

1 a
1 b
1 c
2 c
2 d
2 e

I would try to put some data into each table. And then see if you can run some simple queries. That way you can see if your data structure is correct.

The other thing is you need to consider how you get this data. If you have sectionID as a long but in reality all sections have unique id like xyz-123-vvv then you want to use text.

 

[mitch37]

Hi Maj

I guess the one tink i wont be able to do now is the tree view? is it easy?

Here is my current db.

http://www.4shared.com/file/97010546/3db62cba/EquipHierarchyV4.html

i am actually quite impressed with the way it's turning out and have u to thank for that

 

[MajP]

What I have done is encapsulated a lot of tree view code into a class module. So now it is very easy for me to load any data into a tree view and work with the data in the treeview. However, the code is very complex if you would need to "get under the hood". I think I have made the code very robust that you would not need to fix anything within the class.

I will take a look at you db later today.

 

[mitch37]

no rush...i am just playing around with the db...i think i am learnig as i do so...but we have a lot of other things happening on site that i am attending to, and the db gets back seat...it's kinda annoying but i am sure most people know what i mean?!?