Re Any improvement on these tables please

I suppose I'd better give some more information regarding the above.

Ok,tool type tables a
Electrical, Fire Extinguishers, Hydraulic, Lifting Equipment, Pneumatic,
Precision Equipment Torque Wrenches, and Welding Equipment
Electrical has
ElectricID
Manufacturer,
Product
Serial No
size
Asset No
MPSE No
Last Test Date
Next Test Date
Location
Notes
Common fields a
ID, Manufacturer, Product, Last test Date, Next Test Date, Location

There are about 6/8 other fields which are not common to all tables.

Thanks

On Fri, 02 Jan 2009 20:53:32 +0000, Bob H wrote:

I suppose I'd better give some more information regarding the above.

Ok,tool type tables a
Electrical, Fire Extinguishers, Hydraulic, Lifting Equipment, Pneumatic,
Precision Equipment Torque Wrenches, and Welding Equipment
Electrical has
ElectricID
Manufacturer,
Product
Serial No
size
Asset No
MPSE No
Last Test Date
Next Test Date
Location
Notes
Common fields a
ID, Manufacturer, Product, Last test Date, Next Test Date, Location

There are about 6/8 other fields which are not common to all tables.

Thanks

This is an excellent example of "Subclassing". You have an entity class of
Tools (with a few common fields), and several subclasses for different types
of tools, each with its own unique set of fields.

The two ways to handle this a

1. Create a table with all of the fields needed for any of the subclasses, and
leave them NULL if the field isn't relevant. This isn't quite as bad as it
sounds, since NULL fields take up no space in your database, but it's messy
and makes it hard to document which fields go with which subclass.

2. Use a master Tools table with a primary key for ID and the common fields,
related one-to-one to your subclass tables. The Electrical table would have a
ToolID (primary key, related one to one to the Tools ID) and then whatever
fields are relevant to just Electrical tools; similarly for the other tool
types.

Both solutions have drawbacks and can have complicated queries - but it's a
complicated problem!
--

John W. Vinson [MVP]

John W. Vinson wrote:
On Fri, 02 Jan 2009 20:53:32 +0000, Bob H wrote:

I suppose I'd better give some more information regarding the above.

Ok,tool type tables a
Electrical, Fire Extinguishers, Hydraulic, Lifting Equipment, Pneumatic,
Precision Equipment Torque Wrenches, and Welding Equipment
Electrical has
ElectricID
Manufacturer,
Product
Serial No
size
Asset No
MPSE No
Last Test Date
Next Test Date
Location
Notes
Common fields a
ID, Manufacturer, Product, Last test Date, Next Test Date, Location

There are about 6/8 other fields which are not common to all tables.

Thanks

This is an excellent example of "Subclassing". You have an entity class of
Tools (with a few common fields), and several subclasses for different types
of tools, each with its own unique set of fields.

The two ways to handle this a

1. Create a table with all of the fields needed for any of the subclasses, and
leave them NULL if the field isn't relevant. This isn't quite as bad as it
sounds, since NULL fields take up no space in your database, but it's messy
and makes it hard to document which fields go with which subclass.

2. Use a master Tools table with a primary key for ID and the common fields,
related one-to-one to your subclass tables. The Electrical table would have a
ToolID (primary key, related one to one to the Tools ID) and then whatever
fields are relevant to just Electrical tools; similarly for the other tool
types.

Both solutions have drawbacks and can have complicated queries - but it's a
complicated problem!

From 1.
When you say create a field with all fields needed for the subclasses,
do you mean the tables as subclasses, or the fields for those uncommon
ones (fields).

I'm not really sure what you mean about sub classes, and I have been
trying to find more information on google but its not giving me much I
can understand.

From 2.
Referrring to a Master Tools Table, would that be just a list of the
different types of tools, of which there are 8, or would it have to
include anything else?
The present ID in all of the tables is just an Autonumber, as in
'ElectID' and 'HydraulicID' etc.
I understand it is complicated because it is getting that way for me now.
I am still learning Access 2007, and I am using a book : Access 2007
Inside Out to pick up as much as I can, and even in that book I have not
yet seen any references to subclasses as yet.

Thanks for your time.

On Sat, 03 Jan 2009 12:17:14 +0000, Bob H wrote:

John W. Vinson wrote:
On Fri, 02 Jan 2009 20:53:32 +0000, Bob H wrote:

I suppose I'd better give some more information regarding the above.

Ok,tool type tables a
Electrical, Fire Extinguishers, Hydraulic, Lifting Equipment, Pneumatic,
Precision Equipment Torque Wrenches, and Welding Equipment
Electrical has
ElectricID
Manufacturer,
Product
Serial No
size
Asset No
MPSE No
Last Test Date
Next Test Date
Location
Notes
Common fields a
ID, Manufacturer, Product, Last test Date, Next Test Date, Location

There are about 6/8 other fields which are not common to all tables.

Thanks

This is an excellent example of "Subclassing". You have an entity class of
Tools (with a few common fields), and several subclasses for different types
of tools, each with its own unique set of fields.

The two ways to handle this a

1. Create a table with all of the fields needed for any of the subclasses, and
leave them NULL if the field isn't relevant. This isn't quite as bad as it
sounds, since NULL fields take up no space in your database, but it's messy
and makes it hard to document which fields go with which subclass.

2. Use a master Tools table with a primary key for ID and the common fields,
related one-to-one to your subclass tables. The Electrical table would have a
ToolID (primary key, related one to one to the Tools ID) and then whatever
fields are relevant to just Electrical tools; similarly for the other tool
types.

Both solutions have drawbacks and can have complicated queries - but it's a
complicated problem!

From 1.
When you say create a field with all fields needed for the subclasses,
do you mean the tables as subclasses, or the fields for those uncommon
ones (fields).

I'm not really sure what you mean about sub classes, and I have been
trying to find more information on google but its not giving me much I
can understand.

From 2.
Referrring to a Master Tools Table, would that be just a list of the
different types of tools, of which there are 8, or would it have to
include anything else?
The present ID in all of the tables is just an Autonumber, as in
'ElectID' and 'HydraulicID' etc.
I understand it is complicated because it is getting that way for me now.
I am still learning Access 2007, and I am using a book : Access 2007
Inside Out to pick up as much as I can, and even in that book I have not
yet seen any references to subclasses as yet.

Thanks for your time.

This is the table structure for subclasses.

CREATE TABLE Equipment (
EquipmentID AUTOINCREMENT NOT NULL,
EquipmentType VARCHAR(30) NOT NULL,
CHECK (EquipmentType IN ('Electrical', 'Fire Extinguishers', 'Hydraulic',
'Lifting Equipment', 'Phneumatic','Precision Equipment', 'Torque Wrenches',
'Welding Equipment')), -- use validation rule instead of check
Manufacturer VARCHAR (100) NOT NULL,
Product VARCHAR (50) NOT NULL,
LastTestDate DATETIME NOT NULL,
NextCheckDate DATETIME NOT NULL,
Location VARCHAR (50) NOT NULL,
PRIMARY KEY (EquipmentID, EquipmentType));


CREATE TABLE Electrial (
EquipmentID LONG NOT NULL,
EquipmentType VARCHAR(30) DEFAULT Electrial NOT NULL,
CHECK (EquipmentType = 'Electrical'), -- use validation rule instead
SpecificField1 VARCHAR (50) NOT NULL,
SpecificField2 VARCHAR (50) NOT NULL,
SpecificField3 VARCHAR (50) NOT NULL,
SpecificField4 VARCHAR (50) NOT NULL,
SpecificField5 VARCHAR (50) NOT NULL,
SpecificField6 VARCHAR (50) NOT NULL,
FOREIGN KEY (EquipmentID, EquipmentType)
REFERENCES Equipment (EquipmentID, EquipmentType),
PRIMARY KEY (EquipmentID));


CREATE TABLE [Fire Extinguishers] (
EquipmentID LONG NOT NULL,
EquipmentType VARCHAR(30) DEFAULT Fire Extinguishers NOT NULL,
CHECK (EquipmentType = 'Fire Extinguishers'), -- use validation rule instead
SpecificField1 VARCHAR (50) NOT NULL,
SpecificField2 VARCHAR (50) NOT NULL,
SpecificField3 VARCHAR (50) NOT NULL,
SpecificField4 VARCHAR (50) NOT NULL,
SpecificField5 VARCHAR (50) NOT NULL,
SpecificField6 VARCHAR (50) NOT NULL,
FOREIGN KEY (EquipmentID, EquipmentType)
REFERENCES Equipment (EquipmentID, EquipmentType),
PRIMARY KEY (EquipmentID));

You need a table for each subclass.
SpecificField1, etc. is just to represent some field specific to the subclass.

Here is a simple example.
http://www.psci.net/gramelsp/temp/Tr...nstructors.zip