storing objects with many features

[beginner in table base design]

I am in the process of desiging a database for maintaining and tracking data
on all the equipments in my department.
Each equipment has different features . my problem is on how to store the
equipments as objects in a database. For example... Motor has readings like
grease, amps etc... and engine has readings like thrust, deflection, left
bore reading,The database has around 50 equipments and most of them have
different features. How do I design a table to store all these information
in one table

 

[Stefan Hoffmann]

hi,

I am in the process of desiging a database for maintaining and tracking data
on all the equipments in my department.
Each equipment has different features . my problem is on how to store the
equipments as objects in a database. For example... Motor has readings like
grease, amps etc... and engine has readings like thrust, deflection, left
bore reading,The database has around 50 equipments and most of them have
different features. How do I design a table to store all these information
in one table

You should consider an Entity-Attribute-Value model:

http://en.wikipedia.org/wiki/Entity-Attribute-Value_model

Google for:

http://www.google.de/search?q=entity+attribute+value


mfG
--> stefan <--

 

[John W. Vinson]

I am in the process of desiging a database for maintaining and tracking data
on all the equipments in my department.
Each equipment has different features . my problem is on how to store the
equipments as objects in a database. For example... Motor has readings like
grease, amps etc... and engine has readings like thrust, deflection, left
bore reading,The database has around 50 equipments and most of them have
different features. How do I design a table to store all these information
in one table

This type of information can stretch the relational database paradigm a bit.
Stefan's entity - attribute - value approach is one approach; another is
"Subclassing". The latter uses multilple tables but handles both the need to
keep the information together and still have customized field types.

The way it works is to have a "master" Equipment table:

Equipment
EquipmentID <Primary Key>
Location
ResponsiblePerson
EquipmentType <i.e. motor, engine, computer monitor...>
<other fields that apply to any piece of equipment regardless of type>

This master table would have one-to-one relationships to multiple tables, one
for each type of equipment; e.g.

Motors
EquipmentID <primary key, linked to Equipment.equipmentID>
Amps
Grease
...


John W. Vinson [MVP]

 

[beginner in table base design]

Thank you for the suggestion. I went through the entity attribute- value
article, and feels that it would be a good design approach..
I still donot understand row modelling.

Will the following scenario work?.

each of these equiipment has 1.header information, like , date, check
routine started, check routine completed,
2. check fields (min 10)--- max (30 ) which are basically (yes/no) fields.
3. may or maynot contain technical specifications min(0) max(40). these
techincal spec are different based on aspects of the equipment.

I was considering splitting the three information groups into three tables
with a equipment code to identify the equipments

Will I be able to enter the tech spec of each of the equipments as a
record in one table itself? (Is that what you mean by row modelling??) If
so How??

 

[beginner in table base design]

Thanks for the input. By subclassing each of the tables will have around 30
-40 fields . these fields include header information, check routines ( yes
-no) fields and then technical specification entries. Is that a good design
approach?

 

[Stefan Hoffmann]

hi

Thank you for the suggestion. I went through the entity attribute- value
article, and feels that it would be a good design approach..
I still donot understand row modelling.

Row modelling means, that you store the same information of one entity
or one attribute in a table.
In the EAV model you store information of the same domain. The
information itself cna belong to different entities.

Consider having names for cars and persons. In the EAV model both names
are stored in one table.
In a row model you would have a table CAR and a table PERSON where you
store its names.

The row model is the classical data model, when normalized from 1NF to
5NF and DKNF.

The EAV model uses the idea behind the DKNF. You can say the EAV model
uses somehting like a attribute key form.

each of these equiipment has 1.header information, like , date, check
routine started, check routine completed,
2. check fields (min 10)--- max (30 ) which are basically (yes/no) fields.
3. may or maynot contain technical specifications min(0) max(40). these
techincal spec are different based on aspects of the equipment.

1. Entity: ID (PK)[, meta data...]
The meta data can descript e.g. possible attributes.*

2. Attribute: ID (PK), Name[, meta data...]
The meta data can describe e.g. possible domains (value ranges) or
rules.

3. Value: ID (PK), E_ID, A_ID, Value[, meta data...]
The meta data can contain e.g. order rules.

The Value table is normally split into N tables. For each basic data
type on value table: ValueLong, ValueString, ValueMemo, ValueCurrency.


mfG
--> stefan <--

* This can lead to a four table structure (Entity as Class):

3) Instance: ID (PK), E_ID[, meta data]
The meta data can contain special rules for attributes.

4) Value: ID (PK), I_ID, A_ID, Value[, meta data]

 

[John W. Vinson]

Thanks for the input. By subclassing each of the tables will have around 30
-40 fields . these fields include header information, check routines ( yes
-no) fields and then technical specification entries. Is that a good design
approach?

Probably NOT, actually.

If you have yes/no fields for 30 or 40 check routines, you are embedding a
many (machines) to many (check routines) relationship in each record.

A better design would use three tables:

Machines
MachineID
MachineType
<attributes of the machine>

CheckRoutines
RoutineNo
Description
MachineType <what type of machine needs this check>
<other info about this type of check routine>

MachineCheck
MachineID <which machine is being checked>
RoutineNo <what is it being checked on>
Outcome <maybe a yes/no pass-fail, maybe more detailed result>
<perhaps a field for date checked, or by whom, or other info as needed>


John W. Vinson [MVP]

 

[beginner in table base design]

Appreciate your continued response. I seem to get stuck with the routines no
field. The routines done on each machine is different, and so we will have
an exhaustive routine codes values in the routine No field.

Basically there are routines tasks which are check markd as they are
completed.
Then there are the numerical values which are entered, for some of the
equipments. Changes in these values calls for any required maintenance.

If I categorise the routines are chk routines and data routines, so that I
get two codes values 1- for chk routines, 2- for numerical data;

I will need fields for each equipments to enter the chk values and routine
values.
Its already getting complicated for me ...How do I proceed from here??

 

[John W. Vinson]

Appreciate your continued response. I seem to get stuck with the routines no
field. The routines done on each machine is different, and so we will have
an exhaustive routine codes values in the routine No field.

Basically there are routines tasks which are check markd as they are
completed.
Then there are the numerical values which are entered, for some of the
equipments. Changes in these values calls for any required maintenance.

If I categorise the routines are chk routines and data routines, so that I
get two codes values 1- for chk routines, 2- for numerical data;

I will need fields for each equipments to enter the chk values and routine
values.
Its already getting complicated for me ...How do I proceed from here??

Don't confuse data PRESENTATION - a grid with checkmarks - with data STORAGE.
They are different tasks and require different solutions!

If you have many checkbox fields in an equipment record, then what do you do
when you need to add a new check routine? Redesign your table, redesign your
form, rewrite all your queries, redo all your reports? Ouch! Surely not.

Step back a bit. Don't let your *current* data presentation paradigm - a row
of checkboxes - dictate your data structure. If you really want to do so, you
can code a Form to use a row of checkboxes to enter data into the normalized
table - but you'll really benefit from using the normalized structure.

It's likely that your maintenance procedure table will need THREE fields - the
type of equipment, the nature of the check, and the numerical value. I don't
know your business, though - you do!

John W. Vinson [MVP]

 

[beginner in table base design]

Thanks for being patient with my queries. I do need your assistance. I would
as much has possible like to design the database in the ER model, the reason
being that I am more familiar with it. To give you an idea of the machnine
types and checks being done, here is an example

equipment 1

Air conditioner maintenance

a. Unit ok needed
made
a1. Remove,clena and replace air filters ___,______,____
a2. Clena evaporator and condenser coils ___,_______,_____
a3,. verify all safety and control apparatus _____,____,____

b. All motors

b1 Use infrored thrermometer to check all plugs ______,_____,____
b2 Verify no exceessive bearing

Compressor #1 Compressor #2
Amps _____ Amps ______
Megger _____ Megger ______


Evaporator fan

amps _____, megger____



equipment 2

Crown Maintenance

a. lubrication ok needed made

a1. Verify all grase fittins
_______,________,________

a2. Throroughly grease all sheaves ______,_________,__________

Mechanical

b1 Verify sheave grooves _______,________,________
b2. Verify all crown mounting bolts _______,________,________





This is the form structure . but I do understand the difference between
data presentation and data storage.

A daily log of these activities have be maintained in the database. Since
the checks done each machine is different, how can I give a common field name
to the check routines.??

 

[Steve]

TblEquipment
EquipmentID
EquipmentTypeID
EquipmentItem

TblEquipmentType
EquipmentTypeID
EquipmentType

TblEquipmentMaintenance
EquipmentMaintenanceID
EquipmentTypeID
EquipmentMaintenance

TblEquipmentMaintenanceLog
EquipmentMaintenanceLogID
EquipmentID
EquipmentMaintenanceID
EquipmentMaintenanceDate

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)



"beginner in table base design"

 

[John W. Vinson]

This is the form structure . but I do understand the difference between
data presentation and data storage.

Data Presentation is what *you* see.

Data Storage is what *the computer* sees.

A daily log of these activities have be maintained in the database. Since
the checks done each machine is different, how can I give a common field name
to the check routines.??

You don't. The name of the check being done IS DATA, and needs to be stored in
a field in your table (CheckName might be the name of this field).

If you have a different fieldname for each check, then you need to redesign
your Tables, redesign all queries using the table, restructure your form,
redesign your reports - just to add a new check. Ouch!

Instead, *store the name of the check in a table* and display it on the screen
in a textbox, whenever you need it!

I know it's tough to make the mental shift; but just be aware that paper forms
are basically NEVER a good design for tables. Instead, you need to identify
the *logical* relationships between the data that you're storing, and design
tables to fit it. It's MUCH easier to design a Form to present nomrmalized
data than it is to work with data that's constrained to fit a printed form!

John W. Vinson [MVP]

 

[beginner in table base design]

Hello ,I;m back.
I got the design figured out. I'm using row modelling and it fits in very
well. Basically I have three tables one for the equipment master,
2. the transaction header to keep track of the daily maintenance transactions
3. the transaction detail table

I also have other tables to support these main tables such as code
descriptions etc...

Since the equipment checks are varied for each equipment , I use the
attribute (yes/No) field as a column, and make the various checks for each
equipment items as rows. SO far so good.

I would now like to put a front end to this design for accessing data.
Would Visual studio 2005 be a good option. ? Can it be configured with
MsAccess 2003.

 

[John W. Vinson]

Since the equipment checks are varied for each equipment , I use the
attribute (yes/No) field as a column, and make the various checks for each
equipment items as rows. SO far so good.

So far SO BAD.

You've clearly chosen to reject my suggestions, and to use a Spreadsheet
rather than a normalized table. Ok, your choice!

I would now like to put a front end to this design for accessing data.
Would Visual studio 2005 be a good option. ? Can it be configured with
MsAccess 2003.

You can create forms to display the data in VS 2005; for about half the
effort, you can create forms to display the data in Access. Take your pick,
they both work.

John W. Vinson [MVP]