Database Design

[ParyGoal]

First of all I hope in the right forum.

I am trying to design an Access Database that consists of keeping truck of Procedures for handling equipments.

Each Equipment has its own procedure.

There are 9 sections that need to be filled out for each procedure.

Sections are more of details information for each equipment.

Ex: Startup Procedure. Section 3. How to perform a startup procedure on an equipment

Users have to follow a specific set of steps to perform a Startup. The same thing apply when performing a ShutDown Procedure-- Section 4

Each equipment can have only one procedure. 1- many

Tables I have designed so far.


I think there is a one to many relationship for each Procedure. Here is why.

Section 1: We need to specifiy the list of serial numbers of equipments where the same procedures would apply.


Section 2: List Type & Location of Operator Controls. There could be a number of them.


Section 3. Shutdown Procedure
You specify all the procedures and the Lock Type and State to be verified.

Ex: Shutdown Procedure Table data may look like

Procedure Lock Type State Verified
1) Notify Users Lock & tag Visual

2) DISCONNECT EQUp UNLOCKABLE ATTEMPT TO START

3) "" "" ""
ect..




Here are my tables

Equipments Table
EquiID PK
EquipName
SerialNumber


Procedures Table
ProcedureID PK
EquipID FK



ShutDown Procedure Table
ID PK
ProcedureID
Steps..... THere could be number of steps
Startup Procedure Table
ID PK
ProcedureID
Steps..... THere could be number of steps


Documentation Table
ID PK
ProcedureID
Steps..... THere could be number of steps

ect....

Am I going in the right direction?

Lastely how should design the interface based in this database schema?

I will appreciate any help

Thank you

ParyGoal

 

[dhookom]

I am not sure I understand your start up and shut down tables. If they have multiple step fields than I would change them so that each step is a single record. You would add a field that identifies the sequence.

Also, if the start up and shut down table structures are similar, combine them into a single table with a field that identifies whether the step is start up or shut down.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[ParyGoal]

Hi dhookom. Thank you for your reply.
Actually both Startup and ShutDown procedures are part of the nine sections that need to be field. And both contain multiple step fields, but not the same though. So I guess that exclude the possibilty of combining them into a single table.
THe other sections, also have multiple step fields that need to be filled out. Basically each equipment will have a procedure that consists of nine sections. I thought grouping each section in its own table. Is that correct?

So my questions is is the model I presented in my previous message correct?

Is there a better and more effecient way to represent this database model?

Thank you for your input

ParyGoal

 

[dhookom]

I don't think you are grasping normalization. If you have multiple step fields then your tables are not normalized. I assume you have a hierarchy of a piece of equipment has multiple procedures and procedures have multiple steps. This is a couple one to many relationships.

If you don't understand, this consider reviewing some of the links at

http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101.

If you have trouble applying this, come back with questions.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[ParyGoal]

Hi dhookom.

Actually each piece of equipment can have only one procedure.

A procedure consist of 9 sections that need to be filled out.

Each procedure is actually unique from a database design.
I am sorry for the confusion.
Does this make a difference?

I will read the link you sent me.

Thank you so much for your help

 

[dhookom]

Actually each piece of equipment can have only one procedure." Aren't there two...start up and shut down?

What are "sections"? Are these the same as "steps" or does each section contain one or more steps?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[ParyGoal]

Hi dhookom

Each equipment has a section for the startup and one for the shutdown. The section are just instructions on how to perform the action.

Here is how the information is actually entered manually by section. Each procedure contain the same section. Each equipment has its own procedure.

Section 1.
A. IDENTIFICATION OF MACHINE / GROUP OF MACHINES
GENERAL DESCRIPTION ________________________ MANUFACTURER __________________________
MODEL ____________________________________ *SERIAL NO.__ ______________________________
*List serial numbers of equipment where these same procedures would apply, (i.e., equipment of the same type with similar energy sources):
_________________________________________ __________________________________________________
_________________________________________ __________________________________________________
_________________________________________ __________________________________________________
_________________________________________ __________________________________________________
Location of equipment evaluated: _________________________________________________________________

Section 2
C. ENERGY SOURCES
Can the machine be locked out at the main power source? Some machine installations involve complex wiring schemes. A qualified electrician should evaluate machines where necessary to determine if all electrical circuits can be locked out.

Check and/or list energy sources present on this equipment: Electrical _____, Steam _____,
Hydraulic _____, Pneumatic _____, Natural gas _____, Other _____,
Stored energy sources ___________________________
Identify energy sources and location Lockable? Type of lock or energy control
Yes/No device needed
_____________________________________________ _______ ________________________________
_____________________________________________ _______ ________________________________
_____________________________________________ _______ ________________________________
_____________________________________________ _______ ________________________________
_____________________________________________ _______ ________________________________
_____________________________________________ _______ ________________________________
_____________________________________________ _______ ________________________________
_____________________________________________ _______ ________________________________

Section 3
D. SHUTDOWN PROCEDURES
List, in order, the steps necessary to shut down and de-energize the equipment. Be specific. For stored energy, be specific about how the energy will be dissipated or restrained.

Procedure Lock type & location De-energized state to be verified?
YES/NO? How?
________________________________ _________________ ________________________________
________________________________ _________________ ________________________________
________________________________ _________________ ________________________________
________________________________ _________________ ________________________________
________________________________ _________________ ________________________________
________________________________ _________________ ________________________________
________________________________ _________________ ________________________________

 

[dhookom]

I am not changing my opinion/recommendation. Each procedure in section 3 should be a single record in a related table. Section three should be able to allow as many records as required.

It seems you are making the common mistake of creating a table structure based on the "display" of information on a piece of paper.

BTW: I hope you haven't created 6-7 checkbox fields for energy sources...

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[ParyGoal]

Hi dhookom

I completely agree with you on the fact that each procedure in section 3 should be a single record in a related table. And that it should allow as many records as required. Now if I understood what you meant, this what table for section 3 should look, and please correct me if I am wrong?

ShutDownProcedure Table

ShutDownID Autonumber PK
ProcedureID number FK
ShutDownProcedure text
LockType number. FK. Lookuptable
Location text
DeenergizedVerified boolean
HowEnegyStateVerifiedID number. FK. Lookup table


I am trying to allow the implementation of lookup table whenever it is possible. Thus providing a better data entry, and reducing errors.

BTW: I hope you haven't created 6-7 checkbox fields for energy sources.

2) As fas the Energy Stored

I created a lookup table that hold those values, and referening it in related table.

EnerySource Type Table. Lookup

EnergySourceID autonumber PK
EnergyType Text

Doe all these make any sense I hope?

Thank you

ParyGoal

 

[dhookom]

Your ShutDownProcedure Table is much better than storing in multiple fields in the same table.

You should have a junction table to store ProcedureID and EnergySourceID.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[ParyGoal]

You should have a junction table to store ProcedureID and EnergySourceID only?

 

[dhookom]

I'm not sure how your LOTO works. It isn't clear if the energy sources are linked individually to Procedures or if the energey sources are linked to Procedures through the ShutDownProcedure Table.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[TheAceMan1]

How are ya dhookom . . .

This thread is a continuation of thread702-1321922. I asked [blue]ParyGoal[/blue] to post here as a matter of another opinion . . .

See Ya! . . . . . .

 

[dhookom]

TheAceMan1,
Apparently we have serious differences in opinion regarding what the normalized tables should look like.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[TheAceMan1]

ParyGoal . . .

[blue]It seems you are making the common mistake of creating a table structure based on the "display" of information on a piece of paper.[/blue]

I couldn't agree more. Your paper work is truly inducive of poor DB design. However it can be made to fit. All you have to do is stop looking at the paper and look at the design logic were attempting to present.

For instance section1 is purely descriptive and describes groups of equipment (1 or many). This is fine, but for the life of me, if a user is entering data in this section for a specific piece of equipment whats the point in knowing what other different equipment might use this same procedure? Its apparent to me for the user to know this some kind of analysis has to be made, let alone there are 200, 300, 400 or more equipment items to sift thru!

Flipping the above over, when prescribing a procdeure how does one know wether it already exists?

I also repeat . . . if different equipment can use the same procedure or sections thereof . . . your talking Many to Many relationships here!

See Ya! . . . . . .

 

[ParyGoal]

HI TheAceMan1. Good to see you on this forum.

I understand you point. But unfortunately, that's how they want Section 1 to be designed. The other thing, is it more of optional section. They might not have the serial numbers each time. If they did, then yes, it will be a true many-to many relationship.
If we disregard section one, are the other sections/Tables designed properly?

Thank you

ParyGoal

 

[dhookom]

I don't believe you have provided all the table structures for the other sections.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[ParyGoal]

I think I have.
What I am trying to do is this.
Follow the busines rules. Not basing my table design on the structure of the existing paper-based system.
Follow the 1NF, 2NF, and 3NF rules.

I am going to write down the structure for all my tables and how relate to thier respective section, and post them.

Please note. Since one equipment can have only one procedure, and each procedure can be apply to more than one equipment. That's a one to many relationship, not many-to many relationship.
And believe me I am trying to follow the 1NF, 2NF, and 3NF

ParyGoal

 

[dhookom]

Each equipment has a section for the startup and one for the shutdown" and "one equipment can have only one procedure"...

Doesn't this suggest there are two procedures for each piece of equipment? Your form only showed the shut down procedure. What about start up?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[ParyGoal]

I am not sure if we are using the same definition with regard to procedure and sections of the procedure.

Your comments suggests that we should have a procedure for each section. Thus multiple procedures for any given equipment.


I want to represent a procedure as bieng of combinition of the nine sections.

Table Procedure

ProcedureID Autonumber PK
EquipmentID number fk. Comes from the equipment table.
DateCreated Date
DateRivesed date
CreatedByID number fk
RevisedByID number fk


ShutDown Table

ShutDownID Autonumber PK
ProcedureID number FK.
ShutDownProcedure text. Steps
LockType number. FK. lookuptable
Location text. Could be a lookup table.
DeenergizedVerified boolean
HowEnegyStateVerifiedID number. lookup

Sampe Data will look somehting like this

ShutDownID 1
ProcedureID 2
ShutDownProce Notify All Affected People
LockType 1
Location Dispenser
DeergizedVerififed Yes
HowEneryVeried 1

Second Record/Entry

ShutDownID 2
ProcedureID 2
ShutDownProce Close and lock first gate steam vale
LockType 1
Location
DeergizedVerififed Yes
HowEneryVeried 1


And I do the same thing with statup table. and the rest of the section. Basically I treating each section a table since the information is unrelated to each other. The only thing that ties those table is procedureID FK

Is this design wrong?

Thank you

ParyGoal