To Split, or not to split (a table)! That is the question...

[1]

I have the following tables:

1. Equipment: ID, Description, Manufacturer, MachineSerial

2. EquipService: ID, EquipID (lookup), date, Responsible

3. EquipStandardization: ID, EquipID (lookup), date, Responsible

1 - 2 (1-many)
1 - 3 (1-many)

The question is:
At present the equipment table holds the information for all equipment. Some
of it is serviced and some other is standardized.
That has the following effect. Some of the records in the equipment table
relate to the service table, and some other relate to the standardization
table (At present there is not a record in the equipment table that is both
serviced and standardized, and 99% there never will be any).

Which is best:
a) Keep the table schema as is and let the relationships split the equipment
to be serviced from that is to be standardized or,
b) Split the equipment table into two tables (ServEquip, StandEquip) so that
every record of each table relates to every record one the other??

To round it up, what are the pros and cons in each case???

 

[Craig Alexander Morrison]

Unless I have misunderstood your descriptions (as they appear here with only
the information presented here)....

There would appear to be no need for both the EquipService and
EquipStandardisation tables.

Just have one with a Service/Standardisation marker Yes/No assuming they are
100% one or the other. Or have two markers Service Yes/No and
Standardisation Yes/No to cater for the 1% you mention. The markers should
then be used in all queries to ensure that you are looking at all Service or
Standardisation records.

If there is additional data being kept on the different types then two
tables may be better so as to eliminate the proliferation of nulls in the
unneeded fields.

If the date is the changing element consider getting rid of the secondary ID
field and just use the Primary Key of the Equipment table along with the
date to create a two field primary key in the ServiceStandarisation table.
If there can be more than one record for a piece of equipment on the same
date you may continue with what I assume is a compound primary key of
EquipmentID and the ID in the related table. You could also use the date
field but use the time element of it as well.

You may wish to move Manufacturer to another table above the Equipment table
to make the maintenance of Manufacturers simpler.

You may also wish to use the MachineSerial if it can be guaranteed to be
unique and fairly stable as the Primary Key of the Equipment (or Installed
Equipment see below) table. If you have more than one machine with the same
description you may consider having an Equipment table that just contains
the description and the link to Manufacturer and add a new table for
Installed Equipment.

I have developed several Asset and Facility Management systems including
ones for London Underground and HM Government they are each very different
and the correct design is closely related to your particular situation, so
look on the above as a series of possible options that may or may not apply
to your situation.

IOW Only you have all the information, and therefore all the answers. You
should look on the above message as a series of questions/suggestions.

 

[Tim Ferguson]

a) Keep the table schema as is and let the relationships split the
equipment to be serviced from that is to be standardized or,

In priniciple, I cannot see any problem with your design as it is. I am
assuming there are other differences between Services and
Standardisations that you have not mentioned here: NewPartsFitted,
DeviationFromZero and so on. If not, you might well be better off with a
single Interventions table but that is probably up to you.

If 99% of the equipment needs only Service _or_ Standardisation, then you
have to be able to cope with the other 1%. Do they get two records for
two interventions, or do you need a new DualInspection type or what?

It's easy to find out what has never been inspected:

select equipid from equipment
where equipid not in
( select distinct equipid from services
union
select distinct equipid from standardisations
)

and what is due for new servicing (more than six months ago, etc):

select equipid from services
where completeddate < dateadd("m", Date(), -6)

union

select equipid from qryNeverBeenSeen // see above!


and so on.

b) Split the equipment table into two tables (ServEquip, StandEquip)
so that every record of each table relates to every record one the
other??

Yuk: this sounds like a major maintenance nightmare. Keep one Type Of
Thing in one Table!

Hope that helps


Tim F