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

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.

Equipments Table
EquiID PK
EquipName

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

 

[TheAceMan1]

How are ya ParyGoal . . .

For starters, for what you've shown I see [blue]One to One[/blue] relationships all the way around!
[ol][li]The nine sections appear once for each piece of equipment![/li]
[li]Each documentation appears once for each section![/li][/ol]
This dictates one table. Bear in mind [blue]you'll be using memo fields to list your steps and any detail for those steps[/blue], riding you of the [blue]Documentation Table[/blue] & [blue]Procedures Table[/blue].

As a first shot consider the following base table (bearing in mind the memo fields):

[blue][tt][b]    tblEquipment
--------- -----------
FieldName DataType
--------- -----------[/b]
EquipID   LongInteger as PrimaryKey
Sect1     Memo
  "       Memo
Sect5     Memo 
Sect6     Memo
  "       Memo
Sect9     Memo[/tt][/blue]

As a view of how a memo field might look in the form:

[blue]   Step1: . . . . . . . . . . . . . . . .
Documentation for step1: . . . . . . . . . . . . . . . .

   "

   "

   Step12: . . . . . . . . . . . . . . . .
Documentation for step12: . . . . . . . . . . . . . . . .[/blue]

If you wish to show the documentation in a seperate memo field the make it so by adding Doc1 thru Doc9 to the table:

[blue][tt][b]    tblEquipment
--------- -----------
FieldName DataType
--------- -----------[/b]
EquipID   LongInteger as PrimaryKey
Sect1     Memo
Doc1      Memo
  "       Memo
Sect5     Memo 
Doc5      Memo
Sect6     Memo
Doc6      Memo
  "       Memo
Sect9     Memo
Doc9      Memo[/tt][/blue]

For display in the form a [blue]Tab Control[/blue] would allow you to display it all with each section on a seperate tab.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

 

[ParyGoal]

Hi TheAceMan1

I am doing great. Thank you so much for the reply.

I think I missed a couple things. My apology.
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:

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

2) DISCONNECT EQUp UNLOCKABLE ATTEMPT TO START

3) "" "" ""
ect..


Your suggestion to use a tab control is great. That's what I am going to use.

Did I make any sense I hope?

ParyGoal

 

[TheAceMan1]

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

Section 1: We need to [purple]specifiy the list of serial numbers of equipments where the same procedures would apply.[/purple][/blue]

I'm at crossroads with the logic here. Consider:

[blue]A procedure is an instructional critique on what to do with a piece of equipment via [blue]Type & Model[/blue] in question and has nothing to do with the various serial numbers of the same. They all are prescribed the same procedure. My point here is that the instructional should be intact unto itself.[/blue]

So I see your serial numbers in a seperate table . . . and yes . . . on the many side to tblEquipment. And BTW . . . the equipment table should look more like . . .

[blue][tt]tblEquipment
------------
EquiID as PK
Name
Model
Type[/tt][/blue]

. . . and the serial number table:

[blue][tt]tblSerialNo
------------
SerialID as PK
EquipID  as foreignkey to tblEquipment.EquipID
SN[/tt][/blue]

I also understand your need to use the same sections for different model/type machines. This dictates a [blue]Many to Many[blue] relationship where many different machines use one section and many sections can belong to one machine. [blue]Many to Many[/blue] adds uneeded complexity to a db.

If you have chance take a good look at the following:

Fundamentals of Relational Database Design

Normalizing Tables

Table Relationships

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

 

[ParyGoal]

Quote (TheAceMan1):
A procedure is an instructional critique on what to do with a piece of equipment via Type & Model in question and has nothing to do with the various serial numbers of the same. They all are prescribed the same procedure. My point here is that the instructional should be intact unto itself.

I agree with you on that. But section one is actually just part of the procedure writting. It can even be an optional section since it could be difficult at times to specify the serial number for each equipment.

I think I could have one Table. Equipment Table with the following fields.

EquipmentID PK
SerialNumber
Model
Type

Have a PRocedure Table. with the following fields

ProcedureID PK
EquipmentID FK

DateCreated Date
CreatedByID Number fk. Person that created the procedure
DateRevised Date
RevisedBy Number fk


Now from a normalization point of view I can't add the fields for all the sections in the Procedure Table. It will violate the first and second normal form.

Note. Each equipment will have one procedure. Some equipments may share the same procedure. But each procedure is specific to a single Equipment. One to many.

I am just at how I can handle the Detail of each section, all of which are part of the procedure.

I thought of creating table based on each section, which total 9.

Let's say: The ShutDown Procedure. Section 3

Table Design

ShutDownProcedureID PK
ProcedureID fk.
Procedure Text
LocTypeLocation Number. FK Lookuptable
DeenergizedState Number. FK LookUptable


What do you think of this design?
I really appreciate your input?
Thank you

ParyGoal

 

[TheAceMan1]

ParyGoal . . .

For your purposes I can't help being adamant about the serial numbers residing in a seperate table:

[blue]EquipmentID PK
Model
Type

SerialID PK
EquipmentID FK
SN[/blue]

For any equipment type & model the SN's (via EquipmentID) are easily accessible and [blue]the Equipment table more logically fits your schema![/blue]. Is it not easier at first sight to assign a procedure to the group of machines . . .
[blue]Name Toshiba
Model QAD2000
Type AX100[/blue]
. . . [purple]rather than individually to the 200 serial numbers you have of the above?[/purple] . . . and talk about un-normalized! . . . [blue]WOOF![/blue] If you look at each model & type as a group (wether one or many) it should become apparent.

[blue] Some equipments may share the same procedure.[/blue]

Wether its and entire procedure or sections of a procedure this is [blue]strongly indicitive[/blue] of a [purple]Many to Many[/purple] relationship and fits the logic of sharing. Do have a look at this in the Table relationships link I presented.

Also, to get more input initiate a thread in the Microsoft: Access Tables and Relationships Forum. Just don't use the same question. I'll be sure to monitor and pickup.

On & off I'll be going over what you've present.

So . . . [blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .