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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|
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 |