A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

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



 
 
Thread Tools Display Modes
  #1  
Old November 30th, 2005, 02:27 PM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default To Split, or not to split (a table)! That is the question...

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???


  #2  
Old November 30th, 2005, 03:41 PM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default To Split, or not to split (a table)! That is the question...

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???



  #3  
Old November 30th, 2005, 06:37 PM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default To Split, or not to split (a table)! That is the question...

"1" 1@1 wrote in :

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 he 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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Unable to edit records in a form or query Merlin Using Forms 7 May 10th, 2005 02:00 PM
Access combo box-show name, not ID, in table? write on New Users 30 April 30th, 2005 09:11 PM
Table Desing Question Eddie's Bakery and Cafe' Database Design 5 April 11th, 2005 08:54 PM
Manual line break spaces on TOC or Table of tables Eric Page Layout 9 October 29th, 2004 04:42 PM
COMPARE THE TWO TABLES Stefanie General Discussion 0 June 4th, 2004 04:36 PM


All times are GMT +1. The time now is 11:46 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.