Is Data Just Data?

[FerryMary]

If what I am considering is poor DB design I don't want to do it,,so if
someone could give me clue I'd appreciate it.

I have a particular table that I'd like to cull down to fewer fields. It is
my equipment list. It has various fields that enable me to sort entries.

Since I can sort very easily, I'd like to know if it is bad design to use a
field for different items.

For example:
Equip#1 is of SysA and Uses fields: RPM,VOLTS,FLA
Equip#2 is of SysF and Uses fields: HIGHIDLE,FullLOAD,BOM

In place of 6 fields, since datatype is same, could I just have 3 fields:

RPMorHIGHIDLE,VOLTSorFullLOAD,FLAorBOM

If this is not poor design my table would be changed from

43 fields to 30 fields and the number of blank cells would be would be
dimished greatly.

Hoping this was a clear enough,,,,,
Thanks
Mary

 

[John Nurick]

Hi Mary,

Probably this is a bad idea. Data is seldom "just data". The data type
may look the same (Long or Single or whatever) but the actual "domains"
aren't (they're rates of rotation, voltages, etc.) and making fields do
double duty like this complicates matters further down the line.

It's rare for a table in a properly normalised structure to need as many
as even 30 fields. If you tell us a bit more about your table someone
will probably be able to suggest the best way to proceed.

 

[FerryMary]

Thanks for the reply,

I kind of had that feeling in the back of neck, that it might not be a great
idea.

The database covers basically all aspects of a marine maintenance facility.
The table that is getting "out of hand" is my equipment list. Basically it's
just a roster of onboard and onshore equipment for which maintenance is
responsible. My equipment falls into "SystemsInvolved" based on category:

Where all the info is coming from is specifications on each peice of
equipment. (Maybe I should have a tblequipdetails?,,,maybe even by system)

I do have common information on all equipment such as model#s,serial#s,
description and such. The various equipment splits at the system/category
level. Mechanical or Electrical, Facility Equipment, Firefighting equipment,
liferafts(I havn't even given the liferafts their fields yet, aaah)

It is information that changes rarely, but the details just keep piling up.
Should items such as LastServiceDate/Next ServiceDate
LastServiceHours/NextServiceHours ServiceAt be apart?

My Identifier numbers alone take up 15 fields.The remaining numbers are
split about 50-40-10 (mechanical-electrical-firefighting where not mechanical
or electrical)

Thanks for advice. Any more suggestions are most welcomed.
Mary

 

[John Vinson]

Thanks for the reply,

I kind of had that feeling in the back of neck, that it might not be a great
idea.

The database covers basically all aspects of a marine maintenance facility.
The table that is getting "out of hand" is my equipment list. Basically it's
just a roster of onboard and onshore equipment for which maintenance is
responsible. My equipment falls into "SystemsInvolved" based on category:

Where all the info is coming from is specifications on each peice of
equipment. (Maybe I should have a tblequipdetails?,,,maybe even by system)

I do have common information on all equipment such as model#s,serial#s,
description and such. The various equipment splits at the system/category
level. Mechanical or Electrical, Facility Equipment, Firefighting equipment,
liferafts(I havn't even given the liferafts their fields yet, aaah)

It is information that changes rarely, but the details just keep piling up.
Should items such as LastServiceDate/Next ServiceDate
LastServiceHours/NextServiceHours ServiceAt be apart?

My Identifier numbers alone take up 15 fields.The remaining numbers are
split about 50-40-10 (mechanical-electrical-firefighting where not mechanical
or electrical)

Thanks for advice. Any more suggestions are most welcomed.
Mary

This sounds like a classic example of "Subclassing": you have a
general class of "equipment" which all shares some attributes (i.e.
responsibility, type, model, serial number, etc.); but the class
consists of multiple subclasses which each have their own unique
attributes.

This situation is the one common case where One to One relationships
are appropriate. You'll want an Equipment table with the common
fields, related one-to-one to multiple detail tables (Liferafts, Fire
Equipment, etc. etc.)

These two tables would have a single-field Primary Key - perhaps an
autonumber in Equipment related to a Long Integer in each detail
table, which would be that table's Primary Key.

The form design for this can be tricky... but that's because the
nature of the data is tricky, and the model is as complex as the
reality!

John W. Vinson[MVP]

 

[FerryMary]

This sounds like a classic example of "Subclassing": you have a
general class of "equipment" which all shares some attributes (i.e.
responsibility, type, model, serial number, etc.); but the class
consists of multiple subclasses which each have their own unique
attributes.

This situation is the one common case where One to One relationships
are appropriate. You'll want an Equipment table with the common
fields, related one-to-one to multiple detail tables (Liferafts, Fire
Equipment, etc. etc.)

These two tables would have a single-field Primary Key - perhaps an
autonumber in Equipment related to a Long Integer in each detail
table, which would be that table's Primary Key.

The form design for this can be tricky... but that's because the
nature of the data is tricky, and the model is as complex as the
reality!

John W. Vinson[MVP]

Thank you so much, I just left an Access 2003 normalization information
link. And as you probably guessed I'm abnormal. Fortunately I have
great "real-life" input from mechanics, electricians ,fire systems inspectors
etc regarding the equipment itself. Bad design in my other tables is minimal
and I'll have it cleared quickly. I'm going to fix the little stuff first,
then tackle the equipment table.

Thanks Again!
Mary-ah my mind is racing now.