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.

--
Slainte

Craig Alexander Morrison
Crawbridge Data (Scotland) Limited
"1" 1@1 wrote in message ...
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???